How to use a dynamic excel name in generic formulae.

[vaidmohammed]

Hi,
I use to get a report from our Testers who use to rename their Test Results excel with the current date. The problem is I have created another excel for getting a summary of status with the help of excel formulae.
Since i use to get this Test execution report daily with the different naming conventions, daily I have to carry out changes in my summary excel formulae, as the reference gets changed.
Please help me with some solution formulae.

I have attached sample files in which "TestResults_2020Mar18.xlsx" is Test Execution report, similar to which I use to get from our Testers. And
"TestReport.xlsx" which I have created for getting the summary of executions.

I need a formula solution In TestReport.xlsx excel, so that if I mention the complete path of Test Execution report in cell A2 then system should refer the mentioned excel only.
In this case, if tomorrow testers send me their execution report as "TestResults_2020Mar19.xlsx", so in my report excel I just need to change the name of new test execution report and my below formulae work properly.

 

[Peter Bartholomew]

This is not an area I get into but my immediate reaction would be to avoid links between workbooks.

1. With a VBA macro you could open the file you choose and transfer data from a named range.
2. Another approach would be to do the same thing but using Excel Tables and Power Query.
3. Finally, it is possible to upload the data to a relational database and download the result of a query for analysis.

I hope others with practical experience of doing such tasks join the discussion. Otherwise we are reduced to the blind leading the blind!