View Single Post
  #2   Report Post  
ScottO
 
Posts: n/a
Default

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?
|
|
|