Cross-file changing refrence

[GexcelG]

Hi,
I have a weekly file. just for the eaxmple file names are: 4-Oct.xlsx, 11-Oct.xlsx, 18-Oct.xlsx.
now i am working in the file named 25-Oct.xlsx and i want to have a formula that can take values from the 3 above ( 4-Oct.xlsx, 11-Oct.xlsx, 18-Oct.xls)
i want to use a formula so next week when i'll have file 1-Nov, he will refrence to the previous 3 weeks' files: ( 11-Oct.xlsx, 18-Oct.xlsx, 25-Oct.xlsx).

east part is getting te file name of current file (=cell(filename)) which will return C:\Folder\[25-Oct.xlsx]sheetname

how can i edit this value to have -7 days in the name e.g.: C:\Folder\[18-Oct.xlsx]sheetname, and how can use this text value to creare a refrence to a specific cell within this file:
C:\Folder\[18-Oct.xlsx]sheetname'!$K$12.

thanks !!!

 

[Joe4]

file name of current file (=cell(filename)) which will return C:\Folder\[25-Oct.xlsx]sheetname

OK, let say that the cell with that formula is cell A1. To return a file name for 7 days prior ("18-Oct.xlsx"), you can use this formula:

=TEXT(DATEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"[",REPT(" ",100)),".xlsx]",REPT(" ",100)),100,20)))-7,"d-mmm") & ".xlsx"