Eric58132
07-07-2010, 11:35 AM
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!!!
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!!!