Help with dynamically pulling data from other workbooks

Good evening and thank you in advance for your time and support.

I am creating a daily report that has a date specific name for example.

01-03-2021 Daily Report
02-03-2021 Daily Report
03-03-2021 Daily Report
04-03-2021 Daily Report

And they are all saved in the same folder.

The idea is that each report pulls data from the same 12 cells of the same column of the previous day's reports , so =today()-1 should then be the varriable.
I tried "& or concatenate to break down and put together the address of the other workbooks but nothing is working up to now.
Just to give some heads up I dont want to use indirect as that would mean that the workbooks should be open which I dont want.

Any ideas ?

or ?

For closed workbooks, as you've discovered INDIRECT won't work, even if you do have the full path to the workbook.
You will need VBA (a macro) to put such a formula into cells.
Power Query will be able to do this easily too.