| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Help with Formula Reference Point | Excel Worksheet Functions | |||
| Is it possible? | Excel Worksheet Functions | |||
| "Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
Linear Mode
