Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Concatenate inside a vlookup
Hi, I'm putting together a spreadsheet to automate some summarizing from
other spraedsheets I receive every day. The main roadblock I've run into is that the name of the source sheet changes every day, i.e., "june 24th.xls" & "june 25th.xls" So what I thought I'd to is create some lists with the part of the name that changes and then have a cell concatenate the varialble part of the file name, as below: $A$1(list data): june 24 through june 30 Then a formula to incorporate this into a vlookup =VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"), 5, FALSE) The trouble I'm running into is that the concatenate function sticks quotation marks around what it sticks together, and the vlookup doesn't understand the table_array reference with quotation marks around it. the formula created ends up being: =VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE) Is there any way to pull a string from cells and use it in a vlookup? Or a way of getting rid of the quotation marks around the resulting array_table value? |
#2
|
|||
|
|||
If you take a look at the INDIRECT function, I think it will solve your problem.
Rgds, ScottO "bmclean" wrote in message ... | Hi, I'm putting together a spreadsheet to automate some summarizing from | other spraedsheets I receive every day. The main roadblock I've run into is | that the name of the source sheet changes every day, i.e., "june 24th.xls" & | "june 25th.xls" | So what I thought I'd to is create some lists with the part of the name that | changes and then have a cell concatenate the varialble part of the file name, | as below: | | $A$1(list data): june 24 through june 30 | | Then a formula to incorporate this into a vlookup | | =VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"), 5, | FALSE) | | The trouble I'm running into is that the concatenate function sticks | quotation marks around what it sticks together, and the vlookup doesn't | understand the table_array reference with quotation marks around it. | | the formula created ends up being: | =VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE) | | Is there any way to pull a string from cells and use it in a vlookup? Or a | way of getting rid of the quotation marks around the resulting array_table | value? | | | |
#3
|
|||
|
|||
Note: Indirect will not work unless the other workbook is open. If the other
workbook is open, it will work temporarily. As soon as the other workbook is closed and a calulation takes place the formula will once again return #REF! Biff "ScottO" wrote in message ... If you take a look at the INDIRECT function, I think it will solve your problem. Rgds, ScottO "bmclean" wrote in message ... | Hi, I'm putting together a spreadsheet to automate some summarizing from | other spraedsheets I receive every day. The main roadblock I've run into is | that the name of the source sheet changes every day, i.e., "june 24th.xls" & | "june 25th.xls" | So what I thought I'd to is create some lists with the part of the name that | changes and then have a cell concatenate the varialble part of the file name, | as below: | | $A$1(list data): june 24 through june 30 | | Then a formula to incorporate this into a vlookup | | =VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"), 5, | FALSE) | | The trouble I'm running into is that the concatenate function sticks | quotation marks around what it sticks together, and the vlookup doesn't | understand the table_array reference with quotation marks around it. | | the formula created ends up being: | =VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE) | | Is there any way to pull a string from cells and use it in a vlookup? Or a | way of getting rid of the quotation marks around the resulting array_table | value? | | | |
#4
|
|||
|
|||
I found that as well. I'll have to work around the trouble somehow, but I Was
really hoping to be able to do it without opening the workbook. Thanks to both of you who replied. "Biff" wrote: Note: Indirect will not work unless the other workbook is open. If the other workbook is open, it will work temporarily. As soon as the other workbook is closed and a calulation takes place the formula will once again return #REF! Biff "ScottO" wrote in message ... If you take a look at the INDIRECT function, I think it will solve your problem. Rgds, ScottO "bmclean" wrote in message ... | Hi, I'm putting together a spreadsheet to automate some summarizing from | other spraedsheets I receive every day. The main roadblock I've run into is | that the name of the source sheet changes every day, i.e., "june 24th.xls" & | "june 25th.xls" | So what I thought I'd to is create some lists with the part of the name that | changes and then have a cell concatenate the varialble part of the file name, | as below: | | $A$1(list data): june 24 through june 30 | | Then a formula to incorporate this into a vlookup | | =VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"), 5, | FALSE) | | The trouble I'm running into is that the concatenate function sticks | quotation marks around what it sticks together, and the vlookup doesn't | understand the table_array reference with quotation marks around it. | | the formula created ends up being: | =VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE) | | Is there any way to pull a string from cells and use it in a vlookup? Or a | way of getting rid of the quotation marks around the resulting array_table | value? | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Vlookup | Excel Worksheet Functions | |||
Can you use Concatenate with the If function with vlookup in the i | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |