Hello all - I have run into somewhat of an interesting need of which I'm not sure how to resolve.
The end goal of this project is to create a lookup tool in excel that takes a large worksheet of data and allows the user to filter the output in an iterative fashion, from a high-level down through a more granular level, resulting in a filtered granular list of options to choose from.
Using a simple set of filters on the column headers is not an option because we are trying to make this as clean and simple to use as possible, keeping in mind we will have all manner of people using this file... this would include anyone from a very experienced excel user to someone that may have only used excel once or twice.
For example, we would want a user to first pick from group A, which is the highest level of a group (such as a country). Then the worksheet with the low-level list data would filter and display the options available in that country. Next, the user would pick a more specific item within group B, (such as a State or Region). The list data filters again. Next, the user would pick an ever more specific item within group C, such as City, upon which the list data filters again at the most specific level possible, displaying the final filtered data (such as town).
A couple thoughts around the functionality of this tool:
I have tried doing this with adding data validation lists, but I am having troubles with those lists being dynamic, the values being "searchable" by typing, and the final filtered data displaying in a dynamic manner.
I would really appreciate any and all advice you could offer on this matter... and thank you very much in advance!
The end goal of this project is to create a lookup tool in excel that takes a large worksheet of data and allows the user to filter the output in an iterative fashion, from a high-level down through a more granular level, resulting in a filtered granular list of options to choose from.
Using a simple set of filters on the column headers is not an option because we are trying to make this as clean and simple to use as possible, keeping in mind we will have all manner of people using this file... this would include anyone from a very experienced excel user to someone that may have only used excel once or twice.
For example, we would want a user to first pick from group A, which is the highest level of a group (such as a country). Then the worksheet with the low-level list data would filter and display the options available in that country. Next, the user would pick a more specific item within group B, (such as a State or Region). The list data filters again. Next, the user would pick an ever more specific item within group C, such as City, upon which the list data filters again at the most specific level possible, displaying the final filtered data (such as town).
A couple thoughts around the functionality of this tool:
- The user could type in the value they wish to filter on in each or any of the three groups (A, B, & C), and the list to pick from in that filter would display the options that remain as the typing continues.
- The user can type or select their filter value for any of the three groups and they would get a filtered result. i.e. - each filter group would be able to work independently and would not require all three to be filled out before the final filtered data is displayed.
I have tried doing this with adding data validation lists, but I am having troubles with those lists being dynamic, the values being "searchable" by typing, and the final filtered data displaying in a dynamic manner.
I would really appreciate any and all advice you could offer on this matter... and thank you very much in advance!
