Hey everyone, I have a task at my new job to create a searchable database of blueprints. Rather than using Sort/Filters and CTRL+F, I wanted to try to emulate a drop-down menu style search, such as looking for a car by selecting criteria and then having all the results display. In this case it's Site Type>>Site Name>>Blueprint Type and then you get all the hits.
I got the data validation, list names, writing definitions and indirect feature down and used them for the three levels of drop-down menus, but I wanted the final "results" to display as in multiple cells, not another List.
I found a solution to it, but I either need a different one or need help with the error handling. I basically created a "Results" column where once you make your 3 selections, INDIRECT combines the name of your last two selection and brings up all the maps tagged as that list name (i.e. SpringRidge_TownMaps). I don't know if there's a more efficient way to do this but it worked fine, after I worked out some kinks. To display the results in multiple cells, I made the formula an array and applied it to many cells to assure all the results would fit inside.
The problem is that all the unused spaces come up as #N/A errors and I tried incorporating all the error-handling functions into the formula but nothing worked. Also, if there's only one hit, ALL the cells display the same thing.
I never knew how in-depth you could get with Excel, but I realized quickly that the deeper you go, the bigger headache you get
Thank God I at least had a semester of programming - who knew that would actually ever come in handy!
I couldn't find ANYTHING to help me online...It seems like a very useful way of dealing with database management - Has no one really ever done this in Excel? Maybe I need something more advanced/need to use VBA? This is only excel 2003 so maybe I should request an ugprade?
I'd really appreciate any advice!!!
I got the data validation, list names, writing definitions and indirect feature down and used them for the three levels of drop-down menus, but I wanted the final "results" to display as in multiple cells, not another List.
I found a solution to it, but I either need a different one or need help with the error handling. I basically created a "Results" column where once you make your 3 selections, INDIRECT combines the name of your last two selection and brings up all the maps tagged as that list name (i.e. SpringRidge_TownMaps). I don't know if there's a more efficient way to do this but it worked fine, after I worked out some kinks. To display the results in multiple cells, I made the formula an array and applied it to many cells to assure all the results would fit inside.
The problem is that all the unused spaces come up as #N/A errors and I tried incorporating all the error-handling functions into the formula but nothing worked. Also, if there's only one hit, ALL the cells display the same thing.
I never knew how in-depth you could get with Excel, but I realized quickly that the deeper you go, the bigger headache you get
Thank God I at least had a semester of programming - who knew that would actually ever come in handy!I couldn't find ANYTHING to help me online...It seems like a very useful way of dealing with database management - Has no one really ever done this in Excel? Maybe I need something more advanced/need to use VBA? This is only excel 2003 so maybe I should request an ugprade?
I'd really appreciate any advice!!!
