HELP!! How to formula the "sheet" link

[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) ..... up to sheet 50...

Can someone help me to automatic it in anyway?

Thank you very much!!!!
Nicole

 

[Chip Pearson]

Try a formula like the following:

=SUM(INDIRECT("Sheet"&TEXT(ROW()-4+2,"00")&"'!$H$16:$H$21"),INDIRECT("Sheet"&TEXT(ROW()-4+2,"00")&"!$H$26"))

Change both instances of 4 to the row number in which you enter the
formula. Change both instances of the 2 to the first number of
SheetNN. Copy this formula down for 50 rows.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Max]

Replace this "start" formula:

=SUM('sheet02'!$H$16:$H$21,'sheet02'!$H$26)

with this equivalent using INDIRECT & ROWS as the incrementer
=SUM(INDIRECT("'Sheet"&TEXT(ROWS($1:1)+1,"00")&"'!H16:H21"),INDIRECT("'Sheet"&TEXT(ROWS($1:1)+1,"00")&"'!H26"))
then just copy it down as far as required (by 50 rows or so)
Success? hit the YES below