Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing a different tab by referencing a list in the current s
One workbook, many different sheets/tabs.
Lets say I have a 'summary' sheet and then the names of the sheets after that start with 'one', 'two', 'three', and so on. Cell J49 in every sheet has a certain piece of data I need to reference in the 'summary' sheet. Back in the 'summary' sheet there is a list of all the other sheet names, column A. In column B of the 'summary' sheet I need to reference J49 from the appropriate sheet next to the list of all the sheet names in column A. Right now, I have in column B one!$J$49, two!$J$49 and so on written in every cell. I was trying to see if I could put in something like A1!$J$49 in the cell to reference column A as the sheet name instead of typing it in but that doesn't work. I want to do this so that when I put my cursor on the bottom right of the cell and drag it down the sheet name will be automatically entered in the columnB cell instead of having to type in each name manually. I know this is confusing. I have a fair bit of excel experience and I'm stuck on this one. thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing a different tab by referencing a list in the current s
Try this:
=INDIRECT("'"&A1&"'!J49") Copy down as needed -- Biff Microsoft Excel MVP "Kevin" wrote in message ... One workbook, many different sheets/tabs. Lets say I have a 'summary' sheet and then the names of the sheets after that start with 'one', 'two', 'three', and so on. Cell J49 in every sheet has a certain piece of data I need to reference in the 'summary' sheet. Back in the 'summary' sheet there is a list of all the other sheet names, column A. In column B of the 'summary' sheet I need to reference J49 from the appropriate sheet next to the list of all the sheet names in column A. Right now, I have in column B one!$J$49, two!$J$49 and so on written in every cell. I was trying to see if I could put in something like A1!$J$49 in the cell to reference column A as the sheet name instead of typing it in but that doesn't work. I want to do this so that when I put my cursor on the bottom right of the cell and drag it down the sheet name will be automatically entered in the columnB cell instead of having to type in each name manually. I know this is confusing. I have a fair bit of excel experience and I'm stuck on this one. thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing a different tab by referencing a list in the curre
T. Valko is a genius, but do remember to put dollar signs in front of the J
and 49 if you want those to stay constant. "T. Valko" wrote: Try this: =INDIRECT("'"&A1&"'!J49") Copy down as needed -- Biff Microsoft Excel MVP "Kevin" wrote in message ... One workbook, many different sheets/tabs. Lets say I have a 'summary' sheet and then the names of the sheets after that start with 'one', 'two', 'three', and so on. Cell J49 in every sheet has a certain piece of data I need to reference in the 'summary' sheet. Back in the 'summary' sheet there is a list of all the other sheet names, column A. In column B of the 'summary' sheet I need to reference J49 from the appropriate sheet next to the list of all the sheet names in column A. Right now, I have in column B one!$J$49, two!$J$49 and so on written in every cell. I was trying to see if I could put in something like A1!$J$49 in the cell to reference column A as the sheet name instead of typing it in but that doesn't work. I want to do this so that when I put my cursor on the bottom right of the cell and drag it down the sheet name will be automatically entered in the columnB cell instead of having to type in each name manually. I know this is confusing. I have a fair bit of excel experience and I'm stuck on this one. thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing a different tab by referencing a list in the curre
=INDIRECT("'"&A1&"'!J49")
remember to put dollar signs in front of the J and 49 if you want those to stay constant. $ are not need on J49: $J$49 Because the cell reference is actually a TEXT string when enclosed in quotes: "'!J49", it will not change when copied. -- Biff Microsoft Excel MVP "Kevin" wrote in message ... T. Valko is a genius, but do remember to put dollar signs in front of the J and 49 if you want those to stay constant. "T. Valko" wrote: Try this: =INDIRECT("'"&A1&"'!J49") Copy down as needed -- Biff Microsoft Excel MVP "Kevin" wrote in message ... One workbook, many different sheets/tabs. Lets say I have a 'summary' sheet and then the names of the sheets after that start with 'one', 'two', 'three', and so on. Cell J49 in every sheet has a certain piece of data I need to reference in the 'summary' sheet. Back in the 'summary' sheet there is a list of all the other sheet names, column A. In column B of the 'summary' sheet I need to reference J49 from the appropriate sheet next to the list of all the sheet names in column A. Right now, I have in column B one!$J$49, two!$J$49 and so on written in every cell. I was trying to see if I could put in something like A1!$J$49 in the cell to reference column A as the sheet name instead of typing it in but that doesn't work. I want to do this so that when I put my cursor on the bottom right of the cell and drag it down the sheet name will be automatically entered in the columnB cell instead of having to type in each name manually. I know this is confusing. I have a fair bit of excel experience and I'm stuck on this one. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a Drop-Down List | Excel Discussion (Misc queries) | |||
Referencing a data validation list | Excel Discussion (Misc queries) | |||
If referencing drop-down list | Excel Worksheet Functions | |||
Drop List Referencing | Excel Worksheet Functions | |||
Drop List Referencing | Excel Worksheet Functions |