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?

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

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

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