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

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.

--
Biff
Microsoft Excel MVP


"hsfnwa" wrote in message
...
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

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.

"hsfnwa" wrote:

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