Home |
Search |
Today's Posts |
#1
|
|||
|
|||
worksheet tab name as part of a cell reference
I have a file with 50 worksheets, one for each state of the US.
I am making a master worksheet with a VLOOKUP function to pull data from the other sheets depending on what state name the user types into a certain cell. A VLOOKUP functin will pull the data, like this: =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE) I'd like to make the " 'Alabama'! " part of the function a link to the cell where the user enters the state name. In this way, the function would update itself with whatever state the user wanted to see. However, it seems like worksheet names are static, not linkable. Is there a way to get ths to work? |
#2
|
|||
|
|||
One way
=VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FA LSE) where B2 would hold the state (sheet) name -- Regards, Peo Sjoblom "cwee" wrote in message ... I have a file with 50 worksheets, one for each state of the US. I am making a master worksheet with a VLOOKUP function to pull data from the other sheets depending on what state name the user types into a certain cell. A VLOOKUP functin will pull the data, like this: =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE) I'd like to make the " 'Alabama'! " part of the function a link to the cell where the user enters the state name. In this way, the function would update itself with whatever state the user wanted to see. However, it seems like worksheet names are static, not linkable. Is there a way to get ths to work? |
#3
|
|||
|
|||
Hi Peo
how does the B$10 work in a VLOOKUP statement? Cheers JulieD "Peo Sjoblom" wrote in message ... One way =VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FA LSE) where B2 would hold the state (sheet) name -- Regards, Peo Sjoblom "cwee" wrote in message ... I have a file with 50 worksheets, one for each state of the US. I am making a master worksheet with a VLOOKUP function to pull data from the other sheets depending on what state name the user types into a certain cell. A VLOOKUP functin will pull the data, like this: =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE) I'd like to make the " 'Alabama'! " part of the function a link to the cell where the user enters the state name. In this way, the function would update itself with whatever state the user wanted to see. However, it seems like worksheet names are static, not linkable. Is there a way to get ths to work? |
#4
|
|||
|
|||
Hi Julie,
I assumed the OP put the column index in B10, the formula posted was =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE) -- Regards, Peo Sjoblom "JulieD" wrote in message ... Hi Peo how does the B$10 work in a VLOOKUP statement? Cheers JulieD "Peo Sjoblom" wrote in message ... One way =VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FA LSE) where B2 would hold the state (sheet) name -- Regards, Peo Sjoblom "cwee" wrote in message ... I have a file with 50 worksheets, one for each state of the US. I am making a master worksheet with a VLOOKUP function to pull data from the other sheets depending on what state name the user types into a certain cell. A VLOOKUP functin will pull the data, like this: =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE) I'd like to make the " 'Alabama'! " part of the function a link to the cell where the user enters the state name. In this way, the function would update itself with whatever state the user wanted to see. However, it seems like worksheet names are static, not linkable. Is there a way to get ths to work? |
#5
|
|||
|
|||
Peo, you are a star. worked like a chram. Julie, the B10 just refers to a
column #, which I have as a number at the top of the column. "JulieD" wrote: Hi Peo how does the B$10 work in a VLOOKUP statement? Cheers JulieD "Peo Sjoblom" wrote in message ... One way =VLOOKUP($A14,INDIRECT("'"&B2&"'!A10:O40"),B$10,FA LSE) where B2 would hold the state (sheet) name -- Regards, Peo Sjoblom "cwee" wrote in message ... I have a file with 50 worksheets, one for each state of the US. I am making a master worksheet with a VLOOKUP function to pull data from the other sheets depending on what state name the user types into a certain cell. A VLOOKUP functin will pull the data, like this: =VLOOKUP($A14,'Alabama'!$A$10:$O$40,B$10,FALSE) I'd like to make the " 'Alabama'! " part of the function a link to the cell where the user enters the state name. In this way, the function would update itself with whatever state the user wanted to see. However, it seems like worksheet names are static, not linkable. Is there a way to get ths to work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Using a cell reference to refernce worksheet in another work book | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |