Link cells to multiple external workbooks

[oneyejack77]

I have a workbook that I am using to trend data over time. The cells in the workbooks I want to reference to accomplish this are always the same but the names of the workbook change depending on the date.

If I link one workbook, this is the equation I use

='[Red Alert 10-3-17.xlsm]Summary'!$B$36

I want to be able to pull that formula down to populate the subsequent rows in my trending workbook, but I need the reference to the external workbook to change depending on the date.

Example: if the above formula is in A1, I need A2 to be ='[Red Alert 10-4-17.xlsm]Summary'!$B$36 and A3 to be ='[Red Alert 10-5-17.xlsm]Summary'!$B$36

So on and so on. How can I accomplish this without manually changing each date in the workbook reference of the formula?

Thanks

[63falcondude]

A1 ="'[Red Alert "&TEXT(DATE(2017,10,3)+(ROWS($1:1)-1),"m-d-yy")&".xlsm]Summary'!$B$36"

will return this:
'[Red Alert 10-3-17.xlsm]Summary'!$B$36

and when dragged down, will return this:
'[Red Alert 10-4-17.xlsm]Summary'!$B$36
'[Red Alert 10-5-17.xlsm]Summary'!$B$36
etc.

Try to work that into an INDIRECT function.

[MrShorty]

Though you should be aware that the INDIRECT() function only works when the source workbook is open. If you need this to function on closed workbooks, then we probably need to explore other options.

FWIW, if you have several files that you need to query data from, I think there could be some real value in exploring database queries as a means of extracting data from multiple spreadsheet files rather than worksheet formulas. It might easier (especially after the initial investment to build a good database and learn the query language) in the long run to maintain and use the data in that kind of format than stored as a bunch of different spreadsheet files.

[oneyejack77]

This returns a #REF! error, I'm not sure where the mistake is.

[oneyejack77]

Sorry, I missed MrShorty's post. Indirect function will not work. And I'm just looking for something quick and simple, database query is not an option at this point.