Link changes once the source file name is changed

[mightyujin]

Hi there, i have created a table that compares 2 data, one from this file called "daily_report.xls" and one where the table is, let's say "compare.xls". However i need to change "daily_report.xls" to "date_month.xls" i.e., "14_06.xls" once the comparison is done. I put a VBA that automatically saves to the designated file name, but the source code in the table also changes from
=SUM('C:\\Daily_LCY\[daily_report.xls]A&L'!$B:$Q)
to
=SUM('C:\\Daily_LCY\[14_06.xls]A&L'!$B:$Q).

I don't know how to make the formula to stay constant without changing the file name while the file only changes its name.

Any ideas?

 

[Sektor]

Use INDIRECT.

 

[mightyujin]

How do you use indirect formula? I tried to use as
=SUM(INDIRECT('[daily_report.xls]A&L'!$B:$Q)) but it gives me #REF! error.

 

[Sektor]

A1 cell contains workbook name.

=SUM(INDIRECT("'C:\\Daily_LCY\" & A1 & "A&L'!$B:$Q")

 

[mightyujin]

do you need to put file name in bracket?
i.e., [daily_report.xls] or just daily_report.xls?

mine does not work =/

 

[mightyujin]

It is no use, once the quoted file name changes the file directory changes too. Indirect function changes as well, is there other ways to do it?