HELP REQUIRED: Vlookup VS separate Workbook
 

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

HELP REQUIRED: Vlookup VS separate Workbook
 

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

HELP REQUIRED: Vlookup VS separate Workbook
 

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