Excel - External link to multiple workbooks

Question

My goal is to import specific cell data into a master Excel spreadsheet from many Excel workbooks. For instance, I want to make a list with a row for each workbook by pulling the data of cell B2 from numerous other workbooks. Due to the possibility of future changes to the contents of B2, I want these external links to stay.

I am aware of how to accomplish this manually by altering the formula to match the file name, but is there a more effective method to do this with a workbook name I have manually entered under a column?

Answer 1

Try the INDIRECT() function. But this won't automatically update the data from other workbooks until they are being opened simultaneously.