Populating Multiple Cells from Single Data Validation (Drop Down) List

If you want to limit the choices available for the values that can be entered into a cell, you could use a data validation list.

In the example image below, we are creating a roster for the teachers that are on playground duty during the week.

The data is laid out as a table, and I am using conditional formatting to highlight any duplicates across rows, and down columns. No teacher can be on duty twice in the same day, or be on duty for the same period during the week.

So when I select Claire for A.M. Recess & Lunch on Thursday, my conditional formatting rules warn me. Likewise when I assign Bob to A.M. Recess & Lunch on Friday, and Lunch on Wednesday, the CF rules let me know.

This works nicely but we have to set up data validation for each cell in the table. If we write a little VBA, we can use just one DV list to populate the entire row.

In the image below we can see I am using just one list at the start of each row, and each time I make a selection, the next empty cell in the row is populated.

Not only that, if I make a mistake, I can choose the blank first entry in the list, and this deletes the last value in the row. Or I can press the Delete key to do the same thing.

I can also delete cells in the middle of the row (and they don't have to be adjacent cells), then use the list to fill in the gaps.

This table is using the same conditional formatting rules as the first table.

VBA Code

The VBA uses the Worksheet_Change event so you'll find the code in the Sheet1 module.