lookup limits

D

Don Pistulka

Hi,

I have problem with lookup when the external file is more than two folders deep.

I have a defined name called "Book" that refers to the formula : =INDIRECT(Sheet1!$A$1).

Cell A1 =
'C:\MyPrograms\[Base Case.xls]mvdu'!$a$10:$f$150

The lookup formula is: =VLOOKUP($E11,Book,3,FALSE)


This works fine, but when I move the file to:
'C:\MyPrograms\programs\moreprograms\[Base Case.xls]mvdu'!$a$10:$f$150
I get #REF!

Is there a limit as to how many folders deep either indirect or vlookup can go?

Thanks
Don
 
V

Vasant Nanavati

Hi Don:

I don't believe there is any "folder limit". However, INDIRECT does *not*
work with closed workbooks.

Regards,

Vasant.

Hi,

I have problem with lookup when the external file is more than two folders
deep.

I have a defined name called "Book" that refers to the formula :
=INDIRECT(Sheet1!$A$1).

Cell A1 =
'C:\MyPrograms\[Base Case.xls]mvdu'!$a$10:$f$150

The lookup formula is: =VLOOKUP($E11,Book,3,FALSE)


This works fine, but when I move the file to:
'C:\MyPrograms\programs\moreprograms\[Base Case.xls]mvdu'!$a$10:$f$150
I get #REF!

Is there a limit as to how many folders deep either indirect or
vlookup can go?

Thanks
Don
 
H

Harlan Grove

Don Pistulka said:
I have problem with lookup when the external file is more than two
folders deep.

I have a defined name called "Book" that refers to the formula :
=INDIRECT(Sheet1!$A$1).

Cell A1 =
'C:\MyPrograms\[Base Case.xls]mvdu'!$a$10:$f$150

The lookup formula is: =VLOOKUP($E11,Book,3,FALSE)

This works fine, but when I move the file to:
'C:\MyPrograms\programs\moreprograms\[Base Case.xls]mvdu'!$a$10:$f$150
I get #REF! ....
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
Click to expand...

Don't post in @#$%^&* HTML please. Plain text only. HTML can carry viruses.

It all depends on what you mean by 'move the file'. If the file is open in
Excel, Windows won't let you move it anywhere else. So to move it, it must
be closed.

What is the *complete* *AND* *exact* sequence of steps involved?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Indirect formula errors if file is closed 0
Indirect formula errors if file is closed 1
Using 'indirect' function with full network path 0
Return Formula 8
INDEX MATCH SMALL 13
help w indirect and index,match 3
How to get a blank cell instead of a "0" 2
Excel Lookup 1

Top