Forum Discussion
Linking multiple rows of data to one option on excel data validation list?
For example, I would have CSA 1, CSA 2, AND CSA 3. All 3 would be on different 4 week rotations and be listed on individual rows. If I were to select Department 1 from a drop down list, then all scheduling options for that department would appear, if I were to select Department 2 which has say 4 CSAs instead of 3, then their rotation options would appear.
I hope this makes sense. I know this will involve multiple sheets if possible and I am fairly good with excel, but I am confusing myself trying to figure this out.
2 Replies
What you are describing sounds like dependent data validation lists - visit this page for excellent instructions:
http://www.contextures.com/xlDataVal02.html
Hey Halie,
My guess is that a PivotTable would be the best option here.
1. If your schedules are all in the same table, I would put your cursor in the table, and on the ribbon, on the Insert tab, click PivotTable.
2. On the dialog box that appears, make sure all the data is selected and insert the table on a new sheet.
The next part will be hard to give good instructions on without seeing your data, but I'll do my best :)
3. It sounds like you'll want to see all of your normal data, so I would put the row category (CSAs?) in the Rows box by clicking and dragging from the list above into that box.
4. Then I would put all the columns you would need to see in the columns box by clicking and dragging into the box.
5. The last part is most critical. You can add a filter to the table. Click and drag the filter option into the filter box. In your case, it sounds like you would want the departments to be the filter.
6. Then you should be able to use the dropdown to select the departments and see the data populate below in the table.
Like I said, sorry if this doesn't match your exact data. I hope it can get you close enough. If not, please respond and I'll see if I can get you closer.
