I want to allow a user to create a worksheet using drop down lists for each column.
Column A/Region will be a drop down list showing the Regions, and the selection there will drive the drop down lists for the other columns. I have separate worksheets for Sponsorship, Award, and Competitor lists for each Region. Each of those worksheets is set up with Region as the column header and the list of options for that Region listed in the rows below the Region header. See sample below for Sponsorship with Regions REG_A, REG_B, REG_C :
The lists will have items added/removed periodically and will have different #s of items by region. What is the best way to approach this? I have tried a few things, and they’re not working as expected – Indirect function, setting up Tables and using Index/Match. It could be (likely is!) user error as these are new to me, but I may also not be using the best approach. Any suggestions?
Thanks!
| Region | Sponsorship | Award / Acknowledgment | Competitor |
Column A/Region will be a drop down list showing the Regions, and the selection there will drive the drop down lists for the other columns. I have separate worksheets for Sponsorship, Award, and Competitor lists for each Region. Each of those worksheets is set up with Region as the column header and the list of options for that Region listed in the rows below the Region header. See sample below for Sponsorship with Regions REG_A, REG_B, REG_C :
| REG_A | REG_B | REG_C |
| Team 1 | Ducks | Cavaliers |
| Team 77 | Tourney 1 | Dukes |
| Team 111 | Tourney 11 | Rams |
| Trojans | Trinity | |
| Tourney 17 | ||
The lists will have items added/removed periodically and will have different #s of items by region. What is the best way to approach this? I have tried a few things, and they’re not working as expected – Indirect function, setting up Tables and using Index/Match. It could be (likely is!) user error as these are new to me, but I may also not be using the best approach. Any suggestions?
Thanks!
