Jun 14 2020 04:05 AM
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
Jun 14 2020 04:44 AM
It shall look like
=INDIRECT("'["&(Basis!$C$7)&"]KSB1N'!$O$2:$O$20000")
but INDIRECT works if both files are opened
Jun 14 2020 12:26 PM