HELP!! How to formula the "sheet" link

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

Try a formula like the following:

=SUM(INDIRECT("Sheet"&TEXT(ROW()-4+2,"00")&"'!$H$16:$H$21"),INDIRECT("Sheet"&TEXT(R OW()-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





On Thu, 20 May 2010 15:03:01 -0700, 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) ..... up to sheet 50...

Can someone help me to automatic it in anyway?

Thank you very much!!!!
Nicole

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
--
Max
Singapore
---
"hsfnwa" wrote:
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