Automatically change sequential worksheet names in formulas

[Magnum]

My question can best be explained by using an example:

I have several worksheets with the same format labeled "week 1" through
"week 52" in sequential order. I have another worksheet with the same column
headings as the previous mentioned worksheets. I want to link a cell from
each "week #" worksheet to the summary worksheet in sequential cells.
Basically summarizing data to a single worksheet. I know I can individually
link the cells from each separate worksheet but isn't there an easier way?
Thanks, I appreciate everyone's input.

 

[Pete_UK]

Let's assume that the headings in your summary sheet are in row 1,
with "week 1" in cell B1, and that you want to return data from cell
D4 in each of the sheets. Put this formula in B2 of the summary sheet:

=INDIRECT("'"&B$1&"'!D4")

Copy this across row 2 as required, to get the data from the weekly
sheets. If you need to copy it down, then the D4 will need to be
outside the quotes.

Hope this helps.

Pete

 

[Magnum]

THANK YOU!!!!!! It worked perfectly except for copying the forumla down, but
I modified the "d4" reference using your technique and it works exactly the
way I was wanting. Thanks again.

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete

THANK YOU!!!!!!  It worked perfectly except for copying the forumla down, but
I modified the "d4" reference using your technique and it works exactly the
way I was wanting.  Thanks again.






- Show quoted text -