VLOOKUP and Concatenation

[TesseractE]

I've been looking around on the web for an answer to this quandary, but haven't been able to nail one down yet. Everyone says they get a #VALUE error, but I think I've figured out why that error happens.

Just can't think of how to fix it.

=VLOOKUP("Totals","'G:\Folder Tree\[Reports_"&TEXT($A8,"mm-dd-yy")&"_Team Alpha.xlsx]Daily Productivity'!$B$17:$L$17",1)

Column A has the day's date, and the formula above would be in column B alongside.

The concatenation in VLOOKUP's Table Array boils down to this: "'G:\Folder Tree\[Reports_12-08-11_Team Alpha.xlsx]Daily Productivity'!$B$17:$L$17"

Here's the thing, though: It keeps the double quotes around the Cell Location.

If I were to rewrite it directly like this: =VLOOKUP("Totals",'G:\Folder Tree\[Reports_12-08-11_Team Alpha.xlsx]Daily Productivity'!$B$17:$L$17,1) it works perfectly fine.

Evaluating the initial formula tells me I have the concatenation prepared right, and if I put double quotes around the cell location on the one that otherwise works, it breaks and returns #VALUE. So it seems to be narrowed down to those persistent double quotes.

Any ideas on how to work around this in Excel 2007?

 

[PaddyD]

Hi. Welcome to the board.

You should:

a) review the help file entry for indirect() - that's the native formula you need to convert a text string into a reference (e.g. To a range in a sheet or external file), and
b) search for the VBA code to implement indirect.ext(), because indirect() required the source file to be open, which is less than useful when dealing with external references.

 

[TesseractE]

Hi. Welcome to the board.

Thank you!

You should:

a) review the help file entry for indirect() - that's the native formula you need to convert a text string into of reference (e.g. To a range in a sheet or external file), and
b) search for the VGA code to implement indirect.ext(), because indirect() required the source file to be open, which is less than useful when dealing with external references.

Yeah... INDIRECT's limitations are why I went this route to begin with, and sadly I'm limited myself in that I'm doing this at work, where my ability to do much more than straight formulas is limited.

Additionally, while I'm pretty decent with 'programming' Formulas, VB is a little beyond my ability to handle. I'll have a look at the indirect.ext you mentioned, though, and see if I can get it to work.

In the meantime: Is there a way to use the format_text value in the TEXT function to remove the quotes from a concatenated string?

 

[PaddyD]

You can play with the way the text is getting generated, but it won't help you any - you need to convert the string to a reference, which is a job for indirect() or indirect.ext(), not text functions

 

[TesseractE]

You can play with the way the text is getting generated, but it won't help you any - you need to convert the string to a reference, which is a job for indirect() or indirect.ext(), not text functions

Got it. I appreciate your help!