HELP NEEDED!! Link cells between worksheets

[hsfnwa]

Hi ,

I have a workbook contains 1+50 worksheet, I want to link each sheet number
to sheet one, besides manually change the sheet # in the formula
=SUM('sheet02'!$H$16:$H$21,'sheet02'!$H$26)
=SUM('sheet03'!$H$16:$H$21,'sheet03'!$H$26)
=SUM('sheet04'!$H$16:$H$21,'sheet04'!$H$26)
=SUM('sheet05'!$H$16:$H$21,'sheet05'!$H$26) ..... to sheet 50...

Can someone help me to automatic it in anyway?

Thank you very much!!!!
Nicole

 

[T. Valko]

Try this...

=SUM(INDIRECT("'Sheet"&TEXT(ROWS(A$1:A2),"00")&"'!H16:H21"),INDIRECT("'Sheet"&TEXT(ROWS(A$1:A2),"00")&"'!H26"))

Copy down as needed.

 

[MimiS]

If you just want to sum the same cell in each workbook, then try this.

1) insert blank worksheet before 1st sheet and after last sheet. rename them
something (I use start-> and <-stop)
2) on the summary page in relevant cell, enter =SUM('start->:<-stop'!D23)
where D23 is the cell you're summing.

this is handy when we do budgets. we can include or exclude a dept budget
in a rollup just by pulling it out of the worksheet range start-><-stop.