Excel help - dynamic dropdown lists

[EliseNick]

I'm trying to create a spreadsheet to help prepare a staffing rota. Is there a way of having dynamic dropdown lists in Excel so once I have allocated one person to a job on a given day, I cannot choose them for another?

Thank in advance.

[weeboot]

Not a neat way off the top of my head, but I can think of a number of ways to achieve it.

[tribbles]

I can't think of a way either.

How about highlighting the colour if you've got two assigned?

Mind you, not sure on that one either! Can think of a kludgy way to do it...

[paulrockliffe]

Table with all your staff in, then a second table with an IF statement that returns a blank if the name is in your rota table, then a pivot table based on the second table that is used as the range for your drop-down.

Not sure if that'll work, but it's where I'd start. I think it'll drop all the blanks to the bottom of your drop-down as they're used.

[sideways sid]

Yes it can be done, but how many staff on the rota will determine how usable, or unwieldy it is.

A simpler approach would be to run a check to flag an error msg if the same staff name appears more than once.

[sgrimshaw]

tribbles said:

I can't think of a way either.

How about highlighting the colour if you've got two assigned?

Mind you, not sure on that one either! Can think of a kludgy way to do it...

That's really easy .... highlight the range which needs to be checked and use "conditional formatting" , "highlight cells rules", "duplicate values" then choose how you want to format any duplicates :

[sgrimshaw]

OP - what you want to do is possible, everything you need to know is here (including sample files) :

https://www.contextures.com/xlDataVal03.html

[EliseNick]

Thanks everyone - that's great.