Formula with dynamic tab reference
 

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

Formula with dynamic tab reference
 

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.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"teh_chucksta" wrote in message
...
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

Formula with dynamic tab reference
 

Thanks Earl, it works like a charm!

"Earl Kiosterud" wrote:

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.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"teh_chucksta" wrote in message
...
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

Formula with dynamic tab reference
 

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


"teh_chucksta" wrote:

Thanks Earl, it works like a charm!

"Earl Kiosterud" wrote:

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.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"teh_chucksta" wrote in message
...
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

Formula with dynamic tab reference
 

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

"Ali" wrote:

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


"teh_chucksta" wrote:

Thanks Earl, it works like a charm!

"Earl Kiosterud" wrote:

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.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"teh_chucksta" wrote in message
...
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