Create a dynamic reference in a Name Manager

[pascalrenfer]

Hello,

I would like to know if it is possible to enter a dynamic refernce in a Name manager.

A small example here :

I have a reference to a workbook and the specified sheet and range for a defined name : ='[KSB1N-2020.xlsx]KSB1N'!$C$2:$C$20000

and I would like to modify the workbook link to have the possibility to dynamically select another workbook with the same structure, eg. KSB1N-2019.xlsx

I would create this dynamic reference into another sheet in a specified field... I tried with this INDIRECT with the following formula but it does not work :

=INDIRECT("'["&(Basis!$C$7)&"]KSB1N'!"$O$2:$O$20000)

The field : Basis!$C$7 contains the following information :

=CONCATENATE("KSB1N-";Year_analysis;".xlsx") and shows the following and displays the following result : "KSB1N-2020.xlsx"

Anyone could help me ?

Pascal

[#2]

@pascalrenfer 

It shall look like

=INDIRECT("'["&(Basis!$C$7)&"]KSB1N'!$O$2:$O$20000")

but INDIRECT works if both files are opened

[#3]

Thanks a lot. Do you know anything that would work with closed files a well?