How to Auto Reference Worksheet Tab ID's in Formulas?

[Scott - Key West]

In my workbook (2003) used to track disaster costs, I have some 60 different
worksheets. On the Summary page, the cells pull data from the individual
worksheets.

On the Summary page, there are some 60 rows. Each row pulls data from a
different worksheet; from the same relative cells, just on different sheets.

On the Summary page, the first column is text that identifies which tab
(worksheet) that row's data is from.

My difficulty is having to manually change the formulas in each cell, in
every row, so that it pulls the data from the appropriate worksheet. There's
gotta be a way for the formulas within each row, to know which worksheet to
pull the data from, based upon the Id contents already in the first column of
each row.

If the cells in row 1 pull their respective data from the corresponding
cells on worksheet "5," and the cells in row 2 pull their data from worksheet
"6," I'm having to manually change each cell's formula to reflect the correct
worksheet ID.

For example:

"='21'!$C$5" pulls the data from a specific cell in worksheet "21."

On the next row, I have to manually modify all the formulas as follows so
they reference the same cells on worksheet "22: "='22'!$C$5"

Since "21" & "22" etc. are already identified in the first column of each
row, is there a way the formulas could simply reference the correct
worksheets based upon the reference in the first column?

Something like: "='worksheet Id in 1st column'!$C$5"

Thanks,

Scott Fraser
City of Key West, Florida

 

[Dave Peterson]

=indirect("'" & A1 & "'!c5")
will try to retrieve the value from C5 of the sheet name located in A1.

 

[Gord Dibben]

In A1 enter =INDIRECT(ROW() & "!$C$5")

Copy down to increment the sheets.

Note: you can offset the ROW() to start at sheet 5 if you want.

In A1 enter =INDIRECT(ROW(5:5) & "!$C$5")

Copy down


Gord Dibben MS Excel MVP

 

[Dave Peterson]

But make sure you put the sheet name in apostrophes:

=indirect("'" & row() & "'!c5")
(the $ don't matter, since it's a string)

And I like to use the =row() and adjust that:

=indirect("'" & row() + 5 & "'!c5")

That +5 has to be changed to match the row of the cell getting the formula and
the worksheet from which the value should be retrieved.

 

[Gord Dibben]

I would usually place the apostrophes but why bother when sheets are numbers
1 through 60.

I don't see any spaces in those.

I like to teach others my lazy habits<g>


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I need those apostrophes when the sheets are named like numbers. (Someone
didn't test <hehehe>.)

(And if the name looks like an address, too.)

 

[Gord Dibben]

(Someone didn't test <hehehe>.)

I tested my formula =INDIRECT(ROW() & "!$C$5")

In a workbook with 30 sheets numbered 1 to 30

Different value in C5 of each sheet.

All 30 values were returned.

No apostrophes were required.


Gord

 

[Dave Peterson]

Hmmm.

When I said someone didn't test, I meant me (as far as you know!).

It worked ok for me, too.

But if I built the formula
Select A1 on the 1 sheet
edit|copy
select A2 on any other sheet
edit|paste special|paste link

Then excel will build the formula with the apostrophes.

I'll say it again...

Hmmmm.

 

[Dave Peterson]

ps.

Sorry for casting aspersions in your direction!

 

[Gord Dibben]

Not sure why Excel adds the apsotrophes when you paste link or just use =
sign and click when sheet names are numbers only.

Does not add the apostrophes to a sheetname without spaces.

Hmmmmmm.

I'll treat it as just one of those things Excel does.


Gord