Problems using a variable as a Table_Array in VLOOKUP

[Guest]

I am getting a #N/A when I try to use a variable to represent a table array.
The table array incluses a network path. It looks like this:

=IF(ISNA(VLOOKUP(A29,'S:\CLIENTS\P\Tracking\2005_Tracking\Daily\[Through
7-13-05.xls]how_ordered'!$A:$F,6,FALSE)),0,VLOOKUP(A29,'S:\CLIENTS\P\Tracking\2005_Tracking\Daily\[Through 7-13-05.xls]how_ordered'!$A:$F,6,FALSE))

This formula does work on it's own. What I am trying to do is be able to
change the date automatically. First, I have a row with all the date in a
m-d-yy format. I then use the TEXT function on the date. Next I use
SUBSTITUTE to change 7-14-05 to 7-14-05. This resides in cell U9. When I
use VLOOKUP(A29,U9,6,FALSE) or when I use VLOOKUP(A29,INDIRECT("U9"),6,FALSE)
I receive a #N/A error. If I remove the " " from the idirect statement, I
get a #REF error. I need to be able to increment the U9 when I copy
horizontally ( i.e. V9, W9, X9,...) Any help would be appreciated.

 

[Biff]

Hi!

It sounds like U9 contains some sort of formula:

I then use the TEXT function on the date. Next I use
SUBSTITUTE to change 7-14-05 to 7-14-05. This resides in cell U9.

The argument to Indirect must evaluate to a text representation of a
reference. The way you're using it, it references the formula in cell U9.

But, the biggest set back is that the other file(s) MUST be open for
Indirect to work. Since the other file MUST be open you don't need all of
the path junk.

Since you want to copy/increment the dates that are actually file names,
this means each of those files MUST be open.

So, considering that, do you still want to try this?

Biff

I am getting a #N/A when I try to use a variable to represent a table
array.
The table array incluses a network path. It looks like this:

=IF(ISNA(VLOOKUP(A29,'S:\CLIENTS\P\Tracking\2005_Tracking\Daily\[Through
7-13-05.xls]how_ordered'!$A:$F,6,FALSE)),0,VLOOKUP(A29,'S:\CLIENTS\P\Tracking\2005_Tracking\Daily\[Through
7-13-05.xls]how_ordered'!$A:$F,6,FALSE))

This formula does work on it's own. What I am trying to do is be able to
change the date automatically. First, I have a row with all the date in a
m-d-yy format. I then use the TEXT function on the date. Next I use
SUBSTITUTE to change 7-14-05 to 7-14-05. This resides in cell U9. When I
use VLOOKUP(A29,U9,6,FALSE) or when I use
VLOOKUP(A29,INDIRECT("U9"),6,FALSE)
I receive a #N/A error. If I remove the " " from the idirect statement, I
get a #REF error. I need to be able to increment the U9 when I copy
horizontally ( i.e. V9, W9, X9,...) Any help would be appreciated.