Forum Discussion
dynamically change a reference to a named range in a different workbook
Scenario:
Workbook 1:
named range: range12345
Workbook 2:
formula to reference the named range in workbook 1: ='Workbook 1'!range12345
I would like to have a dropdown (say in C9) where the 12345 part is dictated by the value selected in the dropdown, which would lead to this formula ='Workbook 1'!range & C9
This results in a #NAME! error.
I can get a text version by using =" 'Workbook 1'!range" & C9 but that won't pull the info from the named range.
Any ideas?
1 Reply
Assuming you have the dropdown in Workbook 2 cell C9, and you want to display the value from Workbook 1's named range in cell A1 of Workbook 2.
In Workbook 2, cell A1, use the following formula:
=INDIRECT("'Workbook 1'!range" & C9)
Ensure that Workbook 1 is open when you use this formula in Workbook 2, as INDIRECT needs to access the other workbook in real-time.
Remember that using INDIRECT with external references can make your workbook volatile and slow down calculation, especially if the referenced workbook is large. So use this approach judiciously, and only when necessary.
Additionally, if you want to use this dynamic reference in multiple cells in Workbook 2, you can apply the same formula to those cells with appropriate adjustments for the cell references.
Lastly, please be cautious when using dynamic references with external workbooks, especially if they are shared or accessed by multiple users simultaneously, as it may lead to issues if multiple users try to access or modify the same ranges.The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
