Dynamically update file name references to external workbooks|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi all,

This is driving me loopy.

I've a summary workbook, which links to other workbooks (10~180)

I've been able to create a hyperlink to each workbook using the hyperlink function. 

=HYPERLINK("[C:\Data\Client Name\"&A5&".xlsx]Sheet1!A1",A5) <--sheet name no spaces.
=HYPERLINK("[C:\Data\Client Name\"&A10&".xlsx]'Sheet Two'!A1",A10) <--sheet name with spaces.

This hyperlink is in its own column.

Part 2 - This is where I am stuck. I need to retrieve data from each external workbooks.

The challenge is the number of files. Yes as a one off I could write in a cell: 

='C:\Data\Client Name\[File review 37.xlsx]Sheet1'!$C$55

I need to be able to copy the formula down (and across other columns) and update the external Excel workbook name automatically. The file name is in a column in my summary workbook.

I've been able to use the INDIRECT function, yet this only works with open workbooks.

I've been able to use the INDEX function, but the formula is too complicated (I think) for another person to use/review.

=INDEX('[File review 37.xlsx]Sheet1'!C:C,55,1) <- updating the column/row is perhaps not intuitive. Reviewers are use to see cell references.

I'm sure it's a question of &, single or double quotes - I just can't get them in the correct order.

Thanks