Excel External Reference Based on Cell (Date) in Current Workbook

Question

I've tried searching but I'm not sure if its possible, or how to word the search exactly.

Is it possible to include text from a cell, in my example a date, inside my external reference?

I have an excel that lists the dates of the workdays (M-F) of September in one column (A), and a column next to it (B) where each cell has a similar but different external reference formula that references a daily excel that is generated and saved. Each external reference cell looks up the saved excel for the date that is listed in the Date, (A), column.

Its tedious changing every cells formula manually, when only the date in the name changes.. Below is an example of my Table, and what I'm trying to do:

  A              B
 Date         Formula
9-4-18      =SumProduct(*File path here*/[*Excel Name* **9-4-18**.xlsx]Sheet1'! etc)  
9-5-18      =SumProduct(*File path here*/[*Excel Name* **9-5-18**.xlsx]Sheet1'! etc)  
9-6-18      =SumProduct(*File path here*/[*Excel Name* **9-6-18**.xlsx]Sheet1'! etc)

As you can see the only thing that changes between each formula is the date. So, is it possible to have the formula reference the Date (A) cell to its left to avoid manually changing each formula?

9-4-18      =SumProduct(*File path here*/[*Excel Name* **A2**.xlsx]Sheet1'! etc)

Something like the above is the idea.

Thanks for the help!

I didn't try this, but could indirect do the job, i.e. =INDIRECT(CONCATENATE("SumProduct(";A1;"..."))? At least in LibreOffice, you could do it — Sebastian Rothbucher

Senor Penguin Senor Penguin · Accepted Answer · 2018-09-22T17:33:30

The INDIRECT formula will work, you would have to do it like below. With indirect normally the whole thing would go in quotes but you can chop it up and put a reference cell in there. So just make sure you have everything wrapped in quotes before and after the reference cell.

Without reference cell:
=SUMPRODUCT(INDIRECT("FilePath/[ExcelName10/1/2018.xlsx]Sheet1'!A1")

With reference cell (use this one): =SUMPRODUCT(INDIRECT("FilePath/[ExcelName"&A2&".xlsx]Sheet1'!A1")

Excel External Reference Based on Cell (Date) in Current Workbook

1 Answers