Indirect reference from one worksheet to another
 

I have Worksheet 1 in Workbook 1.

In cell D2, I have a reference to cell $G$5 in Worksheet 2 in Workbook 2.

This works: when I open Worksheet 1, I am asked if I want to resolve
references to external Worksheets.

Or, if I alrady have Worksheet 2 open, the resolution is automatic.

HOWEVER...
In Worksheet 1 I want to put the name of the external Worksheet in column A.
Then in cell D2, I want to use a formula that will resolve to cell $G$5 in
whichever Worksheet is listed in cell A2.

I have been able to construct a formula in cell D2 of Worksheet 1 using the
"INDIRECT" function that looks up the name of the worksheet in column A and
retrieves the value from the named worksheet, but ONLY if the named worksheet
is already open.

How can I do this WITHOUT first opening the referenced spreadsheets?

Hi
with INDIRECT not possible (I huess you mean open workBOOKS and not
workSHEETS). For alternative solutions see:
http://www.dicks-blog.com/archives/2...oks/trackback/

"Bill Sturdevant" wrote:

I have Worksheet 1 in Workbook 1.

In cell D2, I have a reference to cell $G$5 in Worksheet 2 in Workbook 2.

This works: when I open Worksheet 1, I am asked if I want to resolve
references to external Worksheets.

Or, if I alrady have Worksheet 2 open, the resolution is automatic.

HOWEVER...
In Worksheet 1 I want to put the name of the external Worksheet in column A.
Then in cell D2, I want to use a formula that will resolve to cell $G$5 in
whichever Worksheet is listed in cell A2.

I have been able to construct a formula in cell D2 of Worksheet 1 using the
"INDIRECT" function that looks up the name of the worksheet in column A and
retrieves the value from the named worksheet, but ONLY if the named worksheet
is already open.

How can I do this WITHOUT first opening the referenced spreadsheets?

There is no support for that.

Here is a link to some discussion of workarounds.

http://tinyurl.com/2c62u

--
Regards,
Tom Ogilvy


"Bill Sturdevant" wrote in
message ...
I have Worksheet 1 in Workbook 1.

In cell D2, I have a reference to cell $G$5 in Worksheet 2 in Workbook 2.

This works: when I open Worksheet 1, I am asked if I want to resolve
references to external Worksheets.

Or, if I alrady have Worksheet 2 open, the resolution is automatic.

HOWEVER...
In Worksheet 1 I want to put the name of the external Worksheet in column
A.
Then in cell D2, I want to use a formula that will resolve to cell $G$5
in
whichever Worksheet is listed in cell A2.

I have been able to construct a formula in cell D2 of Worksheet 1 using
the
"INDIRECT" function that looks up the name of the worksheet in column A
and
retrieves the value from the named worksheet, but ONLY if the named
worksheet
is already open.

How can I do this WITHOUT first opening the referenced spreadsheets?