Append data based on Date using links

[bunny1122]

Hi All

I have a report that need to append data from other files based on report date.

The source files are daily files with the file name "Daily Report_yyyymmdd", the data is to be updated via links to the source files. I'm currently updating it by copying the link and pasting to row below and change the date manually. Is there a formula/vba to lookup the date in Column A and to just use this formula for the whole Column B without manually changing the date in the link ? Appreciate any inputs, thanks!

Sample data.

Book2.xlsx
	A	B
1	Date	MTM Reserve
2	01/06/22	-189.9390526
3	02/06/22	-194.911566
4	03/06/22	-186.8192117
5	04/06/22
6	05/06/22
7	06/06/22
8	07/06/22
9	08/06/22
10	09/06/22
11	10/06/22
12	11/06/22
13	12/06/22
14	13/06/22
15	14/06/22
16	15/06/22
17	16/06/22
18	17/06/22
19	18/06/22
20	19/06/22
21	20/06/22
22	21/06/22
23	22/06/22
24	23/06/22
25	24/06/22
26	25/06/22
27	26/06/22
28	27/06/22
29	28/06/22
30	29/06/22
31	30/06/22
Sheet1

Cell Formulas
Range	Formula
B2	B2	='C:\Users\10040185B\Documents\[Market Risk Summary Daily Report_20220601.xlsm]Overall Summary'!$D$48
B3	B3	='C:\Users\10040185B\Documents\[Market Risk Summary Daily Report_20220602.xlsm]Overall Summary'!$D$48
B4	B4	='C:\Users\10040185B\Documents\[Market Risk Summary Daily Report_20220603.xlsm]Overall Summary'!$D$48

 

[etaf]

how about something like
=INDIRECT("C:\Users\10040185B\Documents\[Market Risk Summary Daily Report_"&TEXT(A2,"YYYYMMDD")&".xlsm]Overall Summary'!$D$48")