Solved: DropDown Question

[Eric58132]

Hello Board,

The people I'm making a file for (should really be in a database but oh well) have asked me for dropdowns that include all of the available options in an advanced filter. I have a hierchical table and they dislike needing to clear out what existed in the filter previously before knowing what their options are for further report generating.

My solution to this problem is to have dropdowns on the sheet that list all of the available options at each level of the hierarchy. I have loaded up a separate sheet that is connected to an access database and brings over all of the unique values at each level of the hierarchy. I'm pretty sure I need to name all of my ranges individually (each level) and then within the validation settings for each cell I select, simply type '=namedrange' and be done with it, but how do I make the namedrange dynamic in range size? Each range would always lie in just one column, but the number of rows may grow or shrink in size over time. I'm fairly certain that this one isn't difficult for some of you more excel savvy people, but I didn't know how to search google or the like because I couldn't spell out my problem in very few words.

Thank you in advance!!!

[mdmackillop]

You can create a dynamic range name using the Offset function
eg
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

[mdmackillop]

Here's a userform utility I made up to help create dynamic ranges.

[Eric58132]

Hi MD,

thank you for your reply and sorry for the late response: I was traveling yesterday to make a presentation. Anyway, I did what you said and it did exactly what you described. One thing though (an error on my part), there are a lot of duplicates in each column. Is there a way to only list the uniques?

[Bob Phillips]

You would have to create a range of just the uniques and link to that.

[Eric58132]

haha, I'm an idiot. I had done that the first time but just neglected to do it in my new trial. Thank you MD and xld, I appreciate all of your efforts here.