0

Any help would be much appreciated.

My aim is to pull specific cell data from multiple excel workbooks into a master excel spreadsheet. For example I want to pull the contents of cell B2 from many other workbooks to create a list so there would be a row for each workbook. I want these external links to remain as the contents of B2 may change over time.

I know how to do this manually by changing the formula to match the file name, however is there a more efficient way to amend the formula to match a workbook name I've manually input under a column?

I hope this makes sense,

Kind regards,

Thomas

mot375
  • 99
  • 1
  • 13
  • 1
    Try the [INDIRECT()](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261) function. But this won't automatically update the data from other workbooks until they are being opened simultaneously. – Dattel Klauber Jan 13 '23 at 17:08

1 Answers1

1

Dattel is correct in suggesting INDIRECT and also has pointed out that INDIRECT is meant for workbooks that are open. If this is no issue to you then the following formula would get you started.

=INDIRECT("'["&A1&".xlsx]Sheet1'!A1")
A B
Book1 =INDIRECT("'["&A1&".xlsx]Sheet1'!A1")

This would obtain the book name as a reference from cell A1 and get you the same results as manually entering the name (Cell A1 of Book1.xlsx Sheet1).

Take a look at this similar post.

Kevin P.
  • 907
  • 7
  • 18