using Indirect() to reference value in another sheet

[sbmvr]

I am trying to utilize paramemeters in the Indirect() function to look up values in another Excel file which is located every month in another folder reflecting that month, eg "\2011\May2011\". The file name is the same, the path differs from month-to-month. The problem is, when I have assembled the string properly using a serios of Indirect() functions, the value that I need to be picked up from that other Excel file is not retrieved.

So I have in a cell:
= "=' " & INDIRECT("$AC$5") & INDIRECT("$AC$4") & INDIRECT("$AC$6")

that produces the string I need:
='J:\IPB_Finance\GHQ Supplementary Data\2011\May2011\[GPB HMUS Insur.xls]GPB HMUS INSUR'!$Q$7

but I need the numeric value referenced by that string! And that's what I would like you to please comment on.

Thank you all.

 

[Steve=True]

Try:

=Sum(indirect("=' " & INDIRECT("$AC$5") & INDIRECT("$AC$4") & INDIRECT("$AC$6"))

or if you don't want sum, what ever you want.

basically, put another indirect around it when putting it in a function.

 

[MrKowz]

Keep in mind that INDIRECT() cannot create references to closed workbooks. To gain this capability, you need to get the Morefunc addin and use the INDIRECT.EXT() function:

Morefunc Addin: http://xcell05.free.fr/morefunc/english/
INDIRECT.EXT explanation: http://xcell05.free.fr/morefunc/english/indirect.ext.htm

 

[T. Valko]

I am trying to utilize paramemeters in the Indirect() function to look up values in another Excel file which is located every month in another folder reflecting that month, eg "\2011\May2011\". The file name is the same, the path differs from month-to-month. The problem is, when I have assembled the string properly using a serios of Indirect() functions, the value that I need to be picked up from that other Excel file is not retrieved.

So I have in a cell:
= "=' " & INDIRECT("$AC$5") & INDIRECT("$AC$4") & INDIRECT("$AC$6")

that produces the string I need:
='J:\IPB_Finance\GHQ Supplementary Data\2011\May2011\[GPB HMUS Insur.xls]GPB HMUS INSUR'!$Q$7

but I need the numeric value referenced by that string! And that's what I would like you to please comment on.

Thank you all.

The source file MUST be open for INDIRECT to work.

This is usually not desireable especially if there are many files being linked to.

 

[sbmvr]

Try:

=Sum(indirect("=' " & INDIRECT("$AC$5") & INDIRECT("$AC$4") & INDIRECT("$AC$6"))

or if you don't want sum, what ever you want.

basically, put another indirect around it when putting it in a function.

thank you. Assuming that the referenced worksheet is open, using
=SUM(INDIRECT("=' " & INDIRECT("$AC$5") & INDIRECT("$AC$4") & INDIRECT("$AC$6")))
produces #REF! error