VLOOKUP has me stumped when using closed files

tonySA · May 14th, 2002, 12:19 AM

Hi excellers

I'm stumped again and here's the problem, VLOOKUP doesn't want to access information in closed spreadsheets. Is this a known problem or something I'm missing ?

For example:

CELL VALUE
R36 report feb
R38 $A$5:$AF$92
R40 =+R36&"'!"&R38
R43 S:standardsreportsInventoryStocktake 2001-02[stocktake summary stock values.xls]

R45 =+"'" &R43 &R36 &"'!" &R38

The formula ia want to use is
=VLOOKUP($J$4,INDIRECT(R45),8,FALSE)

When the file "stocktake summary stock values.xls" is opened it works, close it and I get #REF!

Any clues ?
Thanks

Tony

Aladin Akyurek · May 14th, 2002, 12:50 AM

Quote:

On 2002-05-13 23:19, tonySA wrote:
Hi excellers

I'm stumped again and here's the problem, VLOOKUP doesn't want to access information in closed spreadsheets. Is this a known problem or something I'm missing ?

For example:

CELL VALUE
R36 report feb
R38 $A$5:$AF$92
R40 =+R36&"'!"&R38
R43 S:standardsreportsInventoryStocktake 2001-02[stocktake summary stock values.xls]

R45 =+"'" &R43 &R36 &"'!" &R38

The formula ia want to use is
=VLOOKUP($J$4,INDIRECT(R45),8,FALSE)

When the file "stocktake summary stock values.xls" is opened it works, close it and I get #REF!

Any clues ?
Thanks

Tony

The use of INDIRECT requires that the target workbooks are open at retrieval time.

sen_edp · May 14th, 2002, 12:54 AM

HELLO TONY

I think that indirect works only with open files.
Take a look at
http://j-walk.com/ss/excel/tips/tip82.htm

tonySA · May 14th, 2002, 12:57 AM

appreciate the feedback and the link
should be able to work something out from that

cheers

Tony

May 14th, 2002, 12:54 AM	#3
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	HELLO TONY I think that indirect works only with open files. Take a look at http://j-walk.com/ss/excel/tips/tip82.htm __________________ Best Regards Andreas

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 12:19 AM	#1
tonySA New Member Join Date: May 2002 Posts: 21	Hi excellers I'm stumped again and here's the problem, VLOOKUP doesn't want to access information in closed spreadsheets. Is this a known problem or something I'm missing ? For example: CELL VALUE R36 report feb R38 $A$5:$AF$92 R40 =+R36&"'!"&R38 R43 S:standardsreportsInventoryStocktake 2001-02[stocktake summary stock values.xls] R45 =+"'" &R43 &R36 &"'!" &R38 The formula ia want to use is =VLOOKUP($J$4,INDIRECT(R45),8,FALSE) When the file "stocktake summary stock values.xls" is opened it works, close it and I get #REF! Any clues ? Thanks Tony

May 14th, 2002, 12:57 AM	#4
tonySA New Member Join Date: May 2002 Posts: 21	appreciate the feedback and the link should be able to work something out from that cheers Tony