I'm trying to create a formula that will look up the same cell only across multiple workbooks. Someone has created a new workbook each week and entered one point of data I want to collect over time in the same cell of each workbook. Each work book has the same name but ends with a different date. Example: "WorkbookABC 1-1-2018.xlsx", then "WorkbookABC 1-8-2018.xlsx",.... etc. I'm guessing I need to use the indirect function. Any ideas on how to create a formula i can just drag down over time which will lookup into the new workbook as well as look back into old workbooks based on the date I want?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Lookup Cell Across Multiple Workbooks
Yeah!
I'm pretty sure that the files all have to be in the same folder, and they can't have any spaces or dashes. Other than that it's easy, this is what I used:
The dates are manually entered in column A, and the formula for cell B3 is =INDIRECT("[WorkbookABC."&A3&".xlsx]Sheet1!$A$3")
Then I was able to click and drag
Hope this helps!
I'm pretty sure that the files all have to be in the same folder, and they can't have any spaces or dashes. Other than that it's easy, this is what I used:
The dates are manually entered in column A, and the formula for cell B3 is =INDIRECT("[WorkbookABC."&A3&".xlsx]Sheet1!$A$3")
Then I was able to click and drag
Hope this helps!
Upvote
0
