Hi All,

I am having issues applying a vlookup that references a separate workbook -
the thing is this workbook neds to remain closed and has a name change
daily, depending on the date.

I have tried a few things such as the following:

=VLOOKUP($E$2,INDIRECT('K:\[IMP Data "& TEXT(TODAY()-2, "yyyymmdd")
&".xls]Data'!$A$1:$AI$65536),5,FALSE)


and

=VLOOKUP($E$2,INDIRECT('K:\INDRECT([IMP Data "& TEXT(TODAY()-2, "yyyymmdd")
&".xls]Data'!)$A$1:$AI$65536),5,FALSE)

But they both meet with either #N/A or #REF!

Is anybody able to assist me with a way I can get the vlookup to consult the
closed workbook?

Thank you in advance for any assistance rendered.

Cheers,
Clint

If the file must remain closed you don't have many options.

There is a free add-in available that might help. It contains a function
called INDIRECT.EXT that works just like the built-in INDIRECT function but
allows you to reference closed files.

Do a Goggle search for Morefunc.xll. Sometimes the authors website is fubar
but there are other sites where you can get the add-in.

--
Biff
Microsoft Excel MVP


"Clinton M James" wrote in message
...
Hi All,

I am having issues applying a vlookup that references a separate
workbook - the thing is this workbook neds to remain closed and has a name
change daily, depending on the date.

I have tried a few things such as the following:

=VLOOKUP($E$2,INDIRECT('K:\[IMP Data "& TEXT(TODAY()-2, "yyyymmdd")
&".xls]Data'!$A$1:$AI$65536),5,FALSE)


and

=VLOOKUP($E$2,INDIRECT('K:\INDRECT([IMP Data "& TEXT(TODAY()-2,
"yyyymmdd") &".xls]Data'!)$A$1:$AI$65536),5,FALSE)

But they both meet with either #N/A or #REF!

Is anybody able to assist me with a way I can get the vlookup to consult
the closed workbook?

Thank you in advance for any assistance rendered.

Cheers,
Clint

Looks like the download link and the forum are both FUBAR at the moment - at
least they were for me using IE 7 a moment ago.

"T. Valko" wrote:

If the file must remain closed you don't have many options.

There is a free add-in available that might help. It contains a function
called INDIRECT.EXT that works just like the built-in INDIRECT function but
allows you to reference closed files.

Do a Goggle search for Morefunc.xll. Sometimes the authors website is fubar
but there are other sites where you can get the add-in.

--
Biff
Microsoft Excel MVP


"Clinton M James" wrote in message
...
Hi All,

I am having issues applying a vlookup that references a separate
workbook - the thing is this workbook neds to remain closed and has a name
change daily, depending on the date.

I have tried a few things such as the following:

=VLOOKUP($E$2,INDIRECT('K:\[IMP Data "& TEXT(TODAY()-2, "yyyymmdd")
&".xls]Data'!$A$1:$AI$65536),5,FALSE)


and

=VLOOKUP($E$2,INDIRECT('K:\INDRECT([IMP Data "& TEXT(TODAY()-2,
"yyyymmdd") &".xls]Data'!)$A$1:$AI$65536),5,FALSE)

But they both meet with either #N/A or #REF!

Is anybody able to assist me with a way I can get the vlookup to consult
the closed workbook?

Thank you in advance for any assistance rendered.

Cheers,
Clint