November 13, 2020
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.
December 7, 2016
I think you would be better off using Power Query, but that is just an assumption, I don't know how your data is.
As you have noticed already, if you want to use the file names in your column to get a dynamic approach you then need to use the INDIRECT function and that requires the files to be open. You can probably find a VBA code that can help you with such scenario.
Regarding the INDEX formula, if you want to show a cell reference you can also write
=INDEX('C:\Data\Client Name\[File review 37.xlsx]Sheet1'!C55,1)
but I don't see any reason to use the INDEX function for this, it doesn't give any help or extra funtionality.
But if you haven't already, do try out Power Query.