Hi experts:

Cant remember how to combine Vlooup with Indirect
This 1 works ok
=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R15000,2)

Then ill have a dynamic range insted of R15000 then use a number in cell B5
this is what i got but it wont work
Can sombody pls. help :-)

=VLOOKUP(B3,INDIRECT("'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R"&B5),2)

INDIRECT won't work on closed files.

The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:


http://xcell05.free.fr/morefunc/english/index.htm


It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.



--
Biff
Microsoft Excel MVP


"excelent" wrote in message
...
Hi experts:

Cant remember how to combine Vlooup with Indirect
This 1 works ok
=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R15000,2)

Then ill have a dynamic range insted of R15000 then use a number in cell
B5
this is what i got but it wont work
Can sombody pls. help :-)

=VLOOKUP(B3,INDIRECT("'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R"&B5),2)

ok thanks ill try that


"T. Valko" skrev:

INDIRECT won't work on closed files.

The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:


http://xcell05.free.fr/morefunc/english/index.htm


It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.



--
Biff
Microsoft Excel MVP


"excelent" wrote in message
...
Hi experts:

Cant remember how to combine Vlooup with Indirect
This 1 works ok
=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R15000,2)

Then ill have a dynamic range insted of R15000 then use a number in cell
B5
this is what i got but it wont work
Can sombody pls. help :-)

=VLOOKUP(B3,INDIRECT("'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R"&B5),2)

You might like to try it like this:

=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B:R,2)

i.e. using full column references, so you don't need to specify the
bottom row, and hence don't need to use INDIRECT. Actually, your range
only needs to be B:C as you are taking the data from the second column
of the lookup table.

Hope this helps.

Pete

On Mar 13, 2:16*pm, excelent
wrote:
ok thanks ill try that

"T. Valko" skrev:



INDIRECT won't work on closed files.

The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:

http://xcell05.free.fr/morefunc/english/index.htm

It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.

--
Biff
Microsoft Excel MVP

"excelent" wrote in message
...
Hi experts:

Cant remember how to combine Vlooup with Indirect
This 1 works ok
=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R15000,2)

Then ill have a dynamic range insted of R15000 then use a number in cell
B5
this is what i got but it wont work
Can sombody pls. help :-)

=VLOOKUP(B3,INDIRECT("'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R"*&B5),2)- Hide quoted text -

- Show quoted text -