• 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.

Linked Drop Downs with Dynamic Ranges

D

dmcgurk

New Member
Problem: I have a Dropdown list that displays a range based on the selection in the first drop down list. I am using the indirect function in the 2nd drop down list to look up a named range - when I use the indirect function to reference a dynamic range (using the offset function) it doesn't work, but when I use the indirect function to reference hard coded ranges, it works fine.


Any idea what I'm doing wrong? Does the indirect function not work with dynamic ranges? Is there another efficient way to update a dynamic range in the second list without the indirect function?


I've uploaded an example workbook to illustrate the problem.

http://www.mediafire.com/view/?14ci5m027b3amgw
 
Hi, dmcgurk!

Do you think this will help?

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list

Regards!
 
I appreciate the post and learned something new, but that won't fix my problem. I'm familiar with using the Indirect function and can get the second drop down to load a correct data set in a non-dynamic capacity. I'm trying to get that 2nd drop down to become dynamic though so as lists of items change in size, the second drop down will update automatically.
 
I did that. The indirect function on the data validation isn't working with a named range that uses an offset formula. That is the problem I'm trying to get around. The second drop down needs to be able to reference difference lists based on what's selected in the first drop down (works with an indirect function linked to a named range that is NOT dynamic) and I want the named range in the second drop down to be dynamic. I've included the link to an example workbook above outlining the problem.
 
Hi, dmcgurk!

And this?

http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/

http://chandoo.org/forums/topic/dynamic-range-in-dependent-validation-list

Regards!


EDIT: If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the preceding one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
 
Hi, dmcgurk!

And if none of above, check this:

http://www.contextures.com/xlDataVal02.html

Regards!
 
That's it, thank you. I had tried searching but didn't come across the contextures link that you posted, and is also in the posts above. Thanks for the assist.
 
@Montrey

Hi!

That's my pleasure... and of course what Luke M and b(ut)ob(ut)hc told me to do anytime I could.

Regards!
 
Hi, dmcgurk!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top