Distribute bedrooms often?

[behedwin]

Hi

I have a spreadsheet where i make lists of who is going visit a big house i administrate.

I do the distribution of rooms, where everyone will sleep.

For exampel room 1 have 2 beds, room 2 have 5 beds, room 3 have 3 bed.

On this spreadsheet i want to set a rule that i cant overbook a room.

The spreadsheet looks like this...

Name | Room |
Kevin | 1 |
Rob | 1 |
Jessica | 2 |
Sara | 2 |
Mike | 1 |

As you can see above, there are 3 ppl placed in room 1
But as i said before there are on 2 beds in room 1

I want the spreadsheet to know how many beds can be in room 1
And when i do a mistake warn me or prevent me from overbooking a room.

How can this be done in a good and smooth way?

[mcmahobt]

Sounds like you could use Data Validation for this. In the ribbon, select Data --> Data Validation. Allow Whole Numbers and set the minimum (presumably zero), and the maximum for that specific room. Now the cell should only allow values less than or equal to that which you have specified.

[behedwin]

It sounds like you are missing the fact that i am putting the room number in the cell to the right of the persons name.

If i use data validation the only thing that does (afaik) is that it prevents me from putting a number that is not allowed in the cell..

But it does not calculate how many spots room 1 already have and warn me about it.



If a room have 2 bed

and i have a long list of names

i give each name a room number where he/she is suppose to sleep.

i want to avoid giving 3 ppl the same room that only have 2 bed.
so in the list i give each person a room to go to
the list should warn me if i give 3 ppl a room that dont have 3 bed or more.

[rw2]

Maybe you could have some helper cells that contain a "countif" formulas. e.g. a cell that counts all the time room 1 is listed etc. Then for each of these cells you could apply conditional formatting so that they give a warning if they go over the limits.

[mcmahobt]

I did misunderstand your problem then. There are multiple ways to tackle this, but rw2 is on the same path I would follow, except I would add in a table that contains the rooms/bed numbers for ease of alteration. I kept a helper column, but you could as easily input the formula into a conditional format. See attached.

[behedwin]

I did misunderstand your problem then. There are multiple ways to tackle this, but rw2 is on the same path I would follow, except I would add in a table that contains the rooms/bed numbers for ease of alteration. I kept a helper column, but you could as easily input the formula into a conditional format. See attached.

Thanks both

This can work great.