HELP NEEDED!! Link cells between worksheets

H

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

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.
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top