Trying to reference a workbook worksheet from a cell content

G

George M

It is hard to explain what I am trying to do in the subject line:

I have two workbooks:
One is "Building Methods"
The other is "Costs"

I have a formulas in the Building Methods workbook that needs to look up a
value in the Costs workbook, but the particular sheet it needs to look up
the costs from has to be gotten from a cell in the Building Methods
worksheet.

For instance here is the formula that works:

=IF(G14="ax",'[Costs.xls]2278'!$D$14,"")

The problem is that I need the "2278" to be picked up form cell G4 rather
than be specified as it is in the formula.

Any way to do this?

Thanks
George
 
D

Dave Peterson

As long as Costs.xls is open:

=IF(G14="ax",INDIRECT("'[costs.xls]"&G4&"'!$D$14"),"")

You'll get an error when you close Costs.xls and the workbook recalculates.

If you have to use a closed workbook, Harlan Grove wrote a userdefined function
that can open another instance of excel and retrieve the value from the closed
workbook there.

http://www.google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

George said:
It is hard to explain what I am trying to do in the subject line:

I have two workbooks:
One is "Building Methods"
The other is "Costs"

I have a formulas in the Building Methods workbook that needs to look up a
value in the Costs workbook, but the particular sheet it needs to look up
the costs from has to be gotten from a cell in the Building Methods
worksheet.

For instance here is the formula that works:

=IF(G14="ax",'[Costs.xls]2278'!$D$14,"")

The problem is that I need the "2278" to be picked up form cell G4 rather
than be specified as it is in the formula.

Any way to do this?

Thanks
George
Click to expand...
 
G

George M

Thanks for the information,

George M


Dave Peterson said:
As long as Costs.xls is open:

=IF(G14="ax",INDIRECT("'[costs.xls]"&G4&"'!$D$14"),"")

You'll get an error when you close Costs.xls and the workbook recalculates.

If you have to use a closed workbook, Harlan Grove wrote a userdefined function
that can open another instance of excel and retrieve the value from the closed
workbook there.

http://www.google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

George said:
It is hard to explain what I am trying to do in the subject line:

I have two workbooks:
One is "Building Methods"
The other is "Costs"

I have a formulas in the Building Methods workbook that needs to look up a
value in the Costs workbook, but the particular sheet it needs to look up
the costs from has to be gotten from a cell in the Building Methods
worksheet.

For instance here is the formula that works:

=IF(G14="ax",'[Costs.xls]2278'!$D$14,"")

The problem is that I need the "2278" to be picked up form cell G4 rather
than be specified as it is in the formula.

Any way to do this?

Thanks
George
Click to expand...
Click to expand...
 
Top