If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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? 
Ads 
#2




If you take a look at the INDIRECT function, I think it will solve your problem.
Rgds, ScottO "bmclean" > wrote in message news  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 > news >  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 > > news > >  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? > >  > >  > >  > > > > > > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Have Vlookup return a Value of 0 instead of #N/A  Mr Mike  Excel Worksheet Functions  4  May 25th 05 04:51 PM 
Vlookup  Using a string within a Vlookup function  Excel Worksheet Functions  1  May 17th 05 03:40 AM 
Can you use Concatenate with the If function with vlookup in the i  simoneaux  Excel Worksheet Functions  2  February 7th 05 09:45 PM 
vlookup data hidden within worksheet  Excel Worksheet Functions  0  January 26th 05 01:09 PM  
Vlookup info being used without vlookup table attached?  Excel Worksheet Functions  0  January 25th 05 11:43 AM 