Detect numbers in a cell range and then change cells based on what numbers are found

Attached is a spreadsheet to help explain, but what I've got is I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.

What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms. Is there any way to do this? I'm not an excel veteran at all, sorry. I don't know enough to even know what I should be searching for. Any advice would be appreciated.

Thanks a ton in advance!

Hi,

Might I suggest conditional formatting?

Take a look. When a room number is allocated, the value isn't removed, but the text colour is changed to white so that you can't see it.

I hope this helps

Wow, thank you all for the help. I would have never thought of some of the ways you did it, and with your direction I was able to do it. Thanks a million! I learned a lot about excel as I tinkered through that. Amazing what you can do if you know how.

No problems Happy to help.

Please don't forget to mark this thread as solved