Formula with dynamic tab reference

[Guest]

I have a workbook with >50 tabs and would like to write a formula that pulls
data from a set cell range but across different tabs. I'd like it written in
a way where a cell (on the summary sheet) occupys a tab name and that cell
will be referenced in the formula so that if I change the name, the formula
knows to pull from a different tab.

No macros please.

Thank you,
Charlie

 

[Earl Kiosterud]

Charlie,

Get cell A1 from sheet named in B4:
=INDIRECT("'" & B4 & "'!A1")

Get cell named in C4 from sheet named in B4.
=INDIRECT("'" & B4 & "'!" & C4)

If you're not sure how to read the quote marks and apostrophes, move the cursor across with
the arrow keys to see one character at a time.

 

[Guest]

Thanks Earl, it works like a charm!

Charlie,

Get cell A1 from sheet named in B4:
=INDIRECT("'" & B4 & "'!A1")

Get cell named in C4 from sheet named in B4.
=INDIRECT("'" & B4 & "'!" & C4)

If you're not sure how to read the quote marks and apostrophes, move the cursor across with
the arrow keys to see one character at a time.

 

[Guest]

Hi earl, sorry i must be missing something. My worksheets are named as staff
names: david, John , peter.
Summary sheet has B1 david, B2 John, B3 peter etc
In column C i need it to read value in H10, from each of the individual
sheets. (overtime for each)
I must be missing something in the quote marks and apostrophes perhaps as i
cant get it to work.
thanks
ali

 

[Guest]

Never mind earl, I have sorted it out. many Thanks