Portion of formula in another cell

[dellschipper]

I am attempting to use a formula which has a part of the formula contained in another cell. The desired formula (which does not work would look like this:

'=SUMPRODUCT(--(CONCATENATE("[ProjectNumber",$A$3,".xlsm]MasterCityData")!A1:A1)*1)

where "$A$3" is the name of the worksheet [using the '=MID(CELL("filename",$C$1),FIND("]",CELL("filename",$C$1))+1,256) technique].

I believe that I have attached two files which explain the problem more thoroughly.

Thanks, Dell.

[NBVC]

To refer to another workbook "indirectly" like that you need to use the INDIRECT function... but, unfortunately, that function only works with source workbooks that are opened, not closed. Is that ok? If not, you can install a free addin that should allow the workbook to be closed.

If either of those are ok, then please also explain what exactly the formula is supposed to do... I don't get why you are using SUMPRODUCT and only referencing A1:A1 which is really just A1 (one cell) and multiplying by 1?

[dellschipper]

I have tried the INDIRECT function and it does not work on closed workbooks (as you also said).

The free addin INDIRECT.EXT (as discussed on a couple of closed threads) says it works on Execl 2007 but I have Excel 2010. I have tried to use it but I might be doing something wrong since I can not see the added functions or it does not install in Excel 2010.

I was trying to use the SUMPRODUCT function since it works on closed workbooks.

I am really trying to get a formula that reads the worksheet name and uses that worksheet name as a part of a workbook name that I can extract cell values from a specific worksheet within that workbook even when that workbook is closed. If this can be done I can make copies of the worksheet and only change the worksheet name and all of the included formulas will be updated automatically.

This approach would be easy to implement and would help to prevent spreadsheet errors. PC memory limitations prevent having all workbooks open at the same time.

[NBVC]

I don't know for sure that the addin works with 2010. The Morefunc home site: http://xcell05.free.fr/english/ doesn't appear to have been updated with 2010 version.

Here are alternatives... but may become complex or inefficient...

http://www.dailydoseofexcel.com/arch...sed-workbooks/

Also, if you are just pointing directly to a cell, doesn't something like this in opened workbooks:

=INDIRECT("'"&[ProjectNumber"&$A$3&".xlsm]MasterCityData!A1") work?

and if you are copying down:

=INDIRECT("'"&[ProjectNumber"&$A$3&".xlsm]MasterCityData!A"&ROWS($A$1:$A1))