I have a spreadsheet that contains a list of data. It has around 40 columns and 2000 rows. Each row contains information about a course and the main bulk of the information relates to the fees for the course. In order to help maintain data integrity and reduce input error I want to implement drop down lists that the users can pick from (rather than free text) and I also want these to be conditional so that the options reduce each time based on what was chosen previously. At the moment this will involve 3 columns, so 3 drop down lists, with the 2nd and 3rd lists being the dependent on the previous list/s.
The idea is that the user selects a code from the 1st drop down list (currently column C) and then there will be two more, dependent, drop down lists to pick from - columns D and E. Ideally I want the 2nd drop down list to be determined by what is selected in the 1st drop down list, and the 3rd drop down list to be determined by what is selected in both the 1st AND 2nd list. The idea being that each list is reduced each time based on the selection in the previous list/s.
I've managed to create the dropdowns where the 2nd and 3rd lists are based on the 1st but this is not ideal. I've done this by creating two separate tables which also means a lot of maintenance and means that the user still has a lot to chose from and so there is more room for error. Both tables have the 1st drop items in the header but one has the data for the 2nd drop down and the other has the data for the 3rd drop down. I have tried to use only one table or a pivot table but I cant find a way to make it work. I also have to take into account that the 2nd and 3rd lists are strings of text, not just one or two words. However in addition to needing it to be easy input for the user I also need the source data to be easily manageable so that it only involves updating one data set, not multiple. This is really important, so ideally I would have one table that is easy to update and that would dynamically update the drop downs too. I don't know if any of this is possible without using VBA.
There is also the issue of error handling which may mean I have to use VBA. For example, if someone makes their selections and then realises they've made a mistake, when they go back and change one of the options the other drops downs won't change until they physically click on that drop down and select a new choice, which also leaves room for error so I'd want the entries to be wiped/reset on change, and maybe even have pop-up messages to help the user (although that's a nice to have at this point!) and ideally I would want them to be dynamic so that so soon as one option is changed all the others change. Again I assume this is VBA territory and would have no idea where to start.
Thank you!!
The idea is that the user selects a code from the 1st drop down list (currently column C) and then there will be two more, dependent, drop down lists to pick from - columns D and E. Ideally I want the 2nd drop down list to be determined by what is selected in the 1st drop down list, and the 3rd drop down list to be determined by what is selected in both the 1st AND 2nd list. The idea being that each list is reduced each time based on the selection in the previous list/s.
I've managed to create the dropdowns where the 2nd and 3rd lists are based on the 1st but this is not ideal. I've done this by creating two separate tables which also means a lot of maintenance and means that the user still has a lot to chose from and so there is more room for error. Both tables have the 1st drop items in the header but one has the data for the 2nd drop down and the other has the data for the 3rd drop down. I have tried to use only one table or a pivot table but I cant find a way to make it work. I also have to take into account that the 2nd and 3rd lists are strings of text, not just one or two words. However in addition to needing it to be easy input for the user I also need the source data to be easily manageable so that it only involves updating one data set, not multiple. This is really important, so ideally I would have one table that is easy to update and that would dynamically update the drop downs too. I don't know if any of this is possible without using VBA.
There is also the issue of error handling which may mean I have to use VBA. For example, if someone makes their selections and then realises they've made a mistake, when they go back and change one of the options the other drops downs won't change until they physically click on that drop down and select a new choice, which also leaves room for error so I'd want the entries to be wiped/reset on change, and maybe even have pop-up messages to help the user (although that's a nice to have at this point!) and ideally I would want them to be dynamic so that so soon as one option is changed all the others change. Again I assume this is VBA territory and would have no idea where to start.
Thank you!!
