Excel formula to create workbook reference for each row

[raymhuber]

Is there any way to create a cell reference that allows you to lookup values from multiple workbooks based on the contents in another cell?

For instance:

instead of typing the below formula and manually editing the date for each row (day)

='F:\Quality\manufacturing worksheets\[10-29-16.xlsx]Template'!$T$25

Is there a way to pull the correct date code from a column and use it in the workbook reference? something like this:


='F:\Quality\manufacturing worksheets\[' & [@[Date code]] & '.xlsx]Template'!$T$255



I know that I could create a vba macro that would do this, but that seems like overkill for this scenario.

 

[lrobbo314]

Something like this

=INDIRECT("'F:\Quality\manufacturing worksheets\["&TEXT($T$25,"mm-dd-yy") & ".xlsx]Template'!$A$1")

 

[raymhuber]

Unfortunately, the indirect function can only reference a workbook if it is open. I need this to work even though the workbooks I will be referencing are closed.