Need Excel Formula Help...I'm stumped.
I have a Excel file that I am working with. the first worksheet, named "Summary" uses the vlookup command to go to the 30 or so other worksheets in the file and retrieves data for me.
the problem is that I have to specify the sheet name manually in each formula because Excel won't automatically pick up on the sheet name and copy it down the page.
Example:
Column A list the exact sheet name I want to reference.
Column B houses the vlookup command getting the data I want.
Cell A1
ST750
Cell B1
=VLOOKUP("Test",ST750!$A$1:$F$65,5,FALSE) <-- this works fine except that I have to manually enter it in every formula because the sheet names changes each time. A2 will have a different worksheet name (i.e. ST1000), then the same for A3, A4, different everytime.
I tried this in cell B1 to automate the process:
=VLOOKUP("Test",A1&"!$A$1:$F$65",5,FALSE)
This does not work. I am trying to force excel to combine the worksheet name and range I want it to look at so I can copy the formula down and save me a lot of time.
Anyone know how to get this done? I am stumped...
Thanks in advance!
the problem is that I have to specify the sheet name manually in each formula because Excel won't automatically pick up on the sheet name and copy it down the page.
Example:
Column A list the exact sheet name I want to reference.
Column B houses the vlookup command getting the data I want.
Cell A1
ST750
Cell B1
=VLOOKUP("Test",ST750!$A$1:$F$65,5,FALSE) <-- this works fine except that I have to manually enter it in every formula because the sheet names changes each time. A2 will have a different worksheet name (i.e. ST1000), then the same for A3, A4, different everytime.
I tried this in cell B1 to automate the process:
=VLOOKUP("Test",A1&"!$A$1:$F$65",5,FALSE)
This does not work. I am trying to force excel to combine the worksheet name and range I want it to look at so I can copy the formula down and save me a lot of time.
Anyone know how to get this done? I am stumped...
Thanks in advance!
Comments
Originally posted by aplnub
I have a Excel file that I am working with. the first worksheet, named "Summary" uses the vlookup command to go to the 30 or so other worksheets in the file and retrieves data for me.
the problem is that I have to specify the sheet name manually in each formula because Excel won't automatically pick up on the sheet name and copy it down the page.
Example:
Column A list the exact sheet name I want to reference.
Column B houses the vlookup command getting the data I want.
Cell A1
ST750
Cell B1
=VLOOKUP("Test",ST750!$A$1:$F$65,5,FALSE) <-- this works fine except that I have to manually enter it in every formula because the sheet names changes each time. A2 will have a different worksheet name (i.e. ST1000), then the same for A3, A4, different everytime.
I tried this in cell B1 to automate the process:
=VLOOKUP("Test",A1&"!$A$1:$F$65",5,FALSE)
This does not work. I am trying to force excel to combine the worksheet name and range I want it to look at so I can copy the formula down and save me a lot of time.
Anyone know how to get this done? I am stumped...
Thanks in advance!
(1) Create a cell (say B1) with text of the target worksheet's lookup range (in your example, the cell would contain the text "ST750!$A$1:$F$65")
(2) Use the VLOOKUP formula VLOOKUP("Test",INDIRECT(B1),5,FALSE)
The indirect formula returns a reference specified by a text string
That is why I am trying to use the worksheet name in A1 and the range (the range will be the same for every sheet) to make this easy.
Do you see what I am saying or am I missing what you are saying?
I wonder if VLOOKUP("Test",INDIRECT(A1&"!$A$1:$F$65"),5,FALSE) would work? I will give it a go.
Originally posted by aplnub
I wonder if VLOOKUP("Test",INDIRECT(A1&"!$A$1:$F$65"),5,FALSE) would work? I will give it a go.
It worked!! Thanks for the heads up with the indirect command. Time to study it and find out what it does exactly.