Retrieve cell value

[Gisela]

I want to retrieve a cell value from a closed workbook but the name of the
workbook varies depending the information entered in the active sheet.

I used the concatenate function in cell B4 to identify the workbook name
based on the information entered. For example: Test_June.xls

In the product cell, I have =indirect(B4) but the function doesn't allow me
to enter the column ($L$1) where the data is in the Test_June workbook.
Please, help!

 

[Dave Peterson]

Open both workbooks (sending and receiving).

Select the sending cell L1 in the sending worksheet in the sending workbook.
Edit|Copy

Select the receiving cell in the receiving worksheet in the receiving workbook.
Edit|paste special|paste link

Close the sending workbook.

Look at that formula (excel will modify it to include the path). That's the
syntax you'll want to use.

But...

If that sending file is not open, then =indirect() returns an error.

(saved from a previous post)

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

 

[Gisela]

How can I use a variable name file in the indirect?

 

[Dave Peterson]

If the file is closed, you'll get a ref error with =indirect().

You'll have to use =indirect.ext().

Build the formula without the =indirect.ext() function.

Then share that formula.

And share the cells that contain the name of the file, folder, sheet, range, ...
and the values in those cells.

The formula will change depending on how you've laid out that data.

It could look like this:
=indirect.ext("'C:\My Documents\excel\[master workbook.xls]" & A1 & "'!$A$1")
or
=indirect.ext("'" & x9 & "\[" & z99 & & ".xls]" & A1 & "'!$A$1")
or lots of things...