Create a dynamic reference in a Name Manager

%3CLINGO-SUB%20id%3D%22lingo-sub-1461511%22%20slang%3D%22en-US%22%3ECreate%20a%20dynamic%20reference%20in%20a%20Name%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461511%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20if%20it%20is%20possible%20to%20enter%20a%20dynamic%20refernce%20in%20a%20Name%20manager.%3C%2FP%3E%3CP%3EA%20small%20example%20here%20%3A%3C%2FP%3E%3CP%3EI%20have%20a%20reference%20to%20a%20workbook%20and%20the%20specified%20sheet%20and%20range%20for%20a%20defined%20name%20%3A%20%3D'%5BKSB1N-2020.xlsx%5DKSB1N'!%24C%242%3A%24C%2420000%3C%2FP%3E%3CP%3Eand%20I%20would%20like%20to%20modify%20the%20workbook%20link%20to%20have%20the%20possibility%20to%20dynamically%20select%20another%20workbook%20with%20the%20same%20structure%2C%20eg.%20KSB1N-2019.xlsx%3C%2FP%3E%3CP%3EI%20would%20create%20this%20dynamic%20reference%20into%20another%20sheet%20in%20a%20specified%20field...%20I%20tried%20with%20this%20INDIRECT%20with%20the%20following%20formula%20but%20it%20does%20not%20work%20%3A%3C%2FP%3E%3CP%3E%3DINDIRECT(%22'%5B%22%26amp%3B(Basis!%24C%247)%26amp%3B%22%5DKSB1N'!%22%24O%242%3A%24O%2420000)%3C%2FP%3E%3CP%3EThe%20field%20%3A%20Basis!%24C%247%20contains%20the%20following%20information%20%3A%3C%2FP%3E%3CP%3E%3DCONCATENATE(%22KSB1N-%22%3BYear_analysis%3B%22.xlsx%22)%20and%20shows%20the%20following%20and%20displays%20the%20following%20result%20%3A%20%22KSB1N-2020.xlsx%22%3C%2FP%3E%3CP%3EAnyone%20could%20help%20me%20%3F%3C%2FP%3E%3CP%3EPascal%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1461511%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461536%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20dynamic%20reference%20in%20a%20Name%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698843%22%20target%3D%22_blank%22%3E%40pascalrenfer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20shall%20look%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDIRECT(%22'%5B%22%26amp%3B(Basis!%24C%247)%26amp%3B%22%5DKSB1N'!%24O%242%3A%24O%2420000%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20INDIRECT%20works%20if%20both%20files%20are%20opened%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1462053%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20dynamic%20reference%20in%20a%20Name%20Manager%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462053%22%20slang%3D%22en-US%22%3EThanks%20a%20lot.%20Do%20you%20know%20anything%20that%20would%20work%20with%20closed%20files%20a%20well%3F%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 Replies
Highlighted

@pascalrenfer 

It shall look like

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

but INDIRECT works if both files are opened

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