UpdateLinks directory location problem

[Brian.Crawford]

I am updating a Workbook from other workbooks (about 10 workbooks with about 200rows by 40columns each to sum up) using links such as (in each cell)

='M:\Development\BC\Test1\[400_Financial_Submission.xlsm]400'!$L$10 + 'M:\Development\BC\Test1\[500_Financial_Submission.xlsm]500'!$L$10
+ 'M:\Development\BC\Test1\[600_Financial_Submission.xlsm]600'!$L$10 etc. etc.

For flexibility (and bevity) I would like the directory part of the above to be stored in a cell (or range name) and append it to the worbook, sheet and range part. I have tried various things but cannot seem to get it to work. It doesn`t seem like my appending the direcory part to the book/sheet/range part... not sure how to do this.

Given that each linked cell is going to consist of the sum of 10 ranges, 1 per workbook/sheet, (ie 10 of the above +`s per cell) is there a simplier approach. All the workbooks to be summed are in the same folder if that helps at all. Also the 1st part of the file name (ie 400) corresponds to the worksheet name I get the data from.

Advice much appreciated
Brian

 

[VoG]

Try for example

=INDIRECT("'&A1"&"\[400_Financial_Submission.xlsm]400'!$L$10")

where A1 contains the path without the final \.

 

[Brian.Crawford]

Could I include everything up to & incuding the sheet name

something like

ie =INDIRECT("'&A1" & "!$L$10")

where A1 =" 'M:\Development\BC\Test1\[400_Financial_Submission.xlsm]400' "

not sure I understand the single quote in the part ("'&A1"


your original suggestion
=INDIRECT("'&A1"&"\[400_Financial_Submission.xlsm]400'!$L$10")
where A1 contains the path without the final \.
Thanks

 

[VoG]

Try

=INDIRECT("'"&A1&"'!$L$10")