Replace a certain part (name of excel file with another excel file) within a formula

Hi all,

my problem is that I want to replace the name of an excel file in a formula with the name of another excel file when a certain condition is met.

The formula is stored lets say in cell A1 and is just an XLOOKUP to retrieve figures that are stored in the "Sheet1.xlsm". Now, I want to replace the [Sheet1.xlsm] in the formula that is stored in A1 with [Sheet2.xlsm] so that the figures are retrieved from the excel file "Sheet2" when the cell J1 is starts with "02" instead of "YE" for instance.

my approach was this:

=IFS(LEFT(J$1,2)="YE";$X$17;LEFT(J$1,2)="02",SUBSTITUTE($X$17,"[Sheet1.xlsm]","[Sheet2.xlsm]"))

X17 = a XLOOKUP function to retrieve figures from Sheet1 (which works).


However, the function always returns the result for when J1 = "YE", even when J1 = "02".


Does anyone know what the problem here is? Or is the SUBSTITUTE function just the wrong one in this case? And if so, is there another way to achieve my desired outcome?

Hope anyone can help me in this regard.

All the best,

ek004

Hi, unfortunately I cannot provide a workbook as the data is confidential.

I also tried it using the indirect function: =IFS(LEFT(J$1,2)="YE";$X$17;LEFT(J$1,2)="02",INDIRECT(SUBSTITUTE($X$17,"[Sheet1.xlsm]","[Sheet2.xlsm]")))

However, I get an #REF! error.

I also read that the other excel file must be opened simultaneously for it to work. Is this correct?