• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dropdown list with linked named range

R

ramzillion

New Member
Will greatly appreciate your help with this please. This is part of a much larger & in-production automation, but we are now stuck with this issue now.


Scenario: Two different excel workbooks A & B. Inside A, we have named range X. Inside B, we have a named range Y that is linked to X in A.


Problem: We want to define a dropdown list in B using Y. It refuses to accept.

Inside dropdown, list, we have tried:

=indirect(Y)
=indirect(A.xls!X)
=Y
Y
indirect(Y)

all five above give errors.

Pls help!
 
Hi, and welcome to the forum ramzillion!

The short answer is, you can't do that. XL does not support the use of the INDIRECT function with an external workbook that is closed. You can sometimes get it to work if both workbooks are open, but that's rarely desired.

The general rule is that if data is dependent on each other, it should all be stored in the same workbook. As there's no limit to the # of worksheets in a file, there's no built-in limit for why you'd need 2 workbooks.
 
Why don't you skip Indirect altogether? If range X will not be changing, just open both wb's, copy range X to range Y and set the wbB data validation to list =Y. If X will be changing, then you could link the cells in your Y range to the cells in your X range and keep automatic updating on with "Ask to update links" off. As long as both workbooks are open Y will stay updated with any changes in X. If wbA will not always open when wbB is open, you can do a Workbook_Open event in wbB to open wbA, update wbB range Y with wbA range X, and close wbA again.
 
Great thanks Luke and Cruiser.

The reason for two files: one of them has company sensitive info, so we want to avoid accidental email out.

And currently we do end up opening both files and I have implemented what Cruiser mentioned (equating cells in B to the range in A). I was just seeking to implement a more elegant solution, because the data size is large and so the equating cells method is taking a long time to update, each time we open the files.

No worries, looks like I will have to live with this. [workbook open and update is not possible because the user has some actions on both files before the quotation is prepared.

Thanks again
 
You must log in or register to reply here.
Back
Top