Reference another workbook (variable name) inside a formula cell

[Guest]

I use a formula to reference a another workbook cell. The other workbook name can changes after sent to another party and returned ammended. Rather than change the name of the spreadsheet, I would prefer to change the reference in a cell of the first spreadsheet.
The formula below activates the lookup if cell c75 is Y. I would like to replace the text [Midwest G1 5000.xls] with a cell containing the name.
=IF($C75="Y",SUMIF('[Midwest G1 5000.xls]Summary'!$B:$B,F$4,'[Midwest G1 5000.xls]Summary'!$J:$J),0)
I have tried using "&" and direct cell references to try to join the complete string to no avail eg if cell b75 contained the string "Midwest G1 5000.xls" and then referenced in the formula.

Is this possible??

 

[Max]

Try INDIRECT(), along these lines ..

Assume D75 contains the text: Midwest G1 5000.xls

Try

:
=IF($C75="Y",SUMIF(INDIRECT("["&D75&"]"&"Summary'!$B:$B"),F$4,INDIRECT(("["&
D75&"]"&"Summary'!$J:$J"),0)

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

I use a formula to reference a another workbook cell. The other workbook

name can changes after sent to another party and returned ammended. Rather
than change the name of the spreadsheet, I would prefer to change the
reference in a cell of the first spreadsheet.

The formula below activates the lookup if cell c75 is Y. I would like to

replace the text [Midwest G1 5000.xls] with a cell containing the name.

=IF($C75="Y",SUMIF('[Midwest G1 5000.xls]Summary'!$B:$B,F$4,'[Midwest G1 5000.xls]Summary'!$J:$J),0)
I have tried using "&" and direct cell references to try to join the

complete string to no avail eg if cell b75 contained the string "Midwest G1
5000.xls" and then referenced in the formula.

 

[Max]

Oops, sorry, missed out your line :

... if cell b75 contained the string "Midwest G1 5000.xls"

Try:

=IF($C75="Y",SUMIF(INDIRECT("["&B75&"]"&"Summary'!$B:$B"),F$4,INDIRECT(("["&
B75&"]"&"Summary'!$J:$J"),0)