1

I'm creating a scheduling spreadsheet in Excel. Lets say we have 5 rooms, two rooms hold 10 people and the other three hold 20.
I'd like to create a list that will show which rooms are available based on group size. So if I have 8 people it the list will show 5 available rooms. But if I have 15 people the list will show only 3 available rooms. I know this can be done with a dependent drop down list. But it doesn't seem overly efficient to create tables for every room with rows for every person they hold. Is there a simpler way? I'd love to just be able to return rows to the drop down IF their size is >= the user's input.

0

There is no problem putting an if in the validation for the drop down list.

FIrst, create the list like usual: Select the cell where you want your drop down list, data -> data validation -> allow list.

Then in the source, you write your conditions. Say that your 5 rooms are listed in R1 to R5, and in A1 you enter how many people you have, then you could have something like:

=IF(A1>10,R3:R5,R1:R5)

Part 2 Nested IFs:

When the conditions get too many to even fit in the box, we need another solution. First and easiest is to try and make it shorter.

=IF($E$96<=3,Sheet1!$A$2:$A$22, IF($E$96<=6, Sheet1!$A$3:$A$22, IF($E$96<= 8, Sheet1!$A$5:$A$22, IF($E$96<= 9, Sheet1!$A$6:$A$22, IF($E$96<=10, Sheet1!$A$7:$A$22, IF($E$96<=15, Sheet1!$A$8:$A$22, IF($E$96<=16,Sheet1!$A$10:$A$22, IF($E$96<=18,Sheet1!$A$11:$A$22, IF($E$96<=20,Sheet1!$A$14:$A$22, IF($E$96<=34,Sheet1!$A$17:$A$22, IF($E$96<=100,Sheet1!$A$18:$A$22,)))))))))))

Is quite long indeed. A few things we can do to make it shorter is to get rid of the absolute reference and use the IFSfunction. The code would then look like:

=IFS(E96<=3,Sheet1!A2:A22, E96<=6, Sheet1!A3:A22, E96<= 8, Sheet1!A5:A22, E96<= 9, Sheet1!A6:A22, E96<=10, Sheet1!A7:A22, E96<=15, Sheet1!A8:A22, E96<=16,Sheet1!A10:A22, E96<=18,Sheet1!A11:A22, E96<=20,Sheet1!A14:A22, E96<=34,Sheet1!A17:A22, E96<=100,Sheet1!A18:A22)

Better, but still too long. Ofc, if we had it on the same sheet, it could be even shorter. But this is no good solution in the long run.

One way to work around the limitation of the input box for the list, is to put the code in a cell, and refer to it by using INDIRECT().

Here is a picture of how it could look: enter image description here

The list is in C5 and simply contains the code =INDIRECT(E3) And in the picture, the list contains Room15-20.

I use a line break in the code of E3 for each check, by using ALT + Enter.

The cell containing the code can be hidden or on another sheet, or whatever you want. You probably want it on the same sheet as the list of rooms, just to keep the clutter down in the code.

Part 3 OFFSET()/MATCH()

One way to make the code a bit more dynamic (but not less complicated) is by using the MATCH() Function to search what rooms we can use.

In this case, we need to specify the number of people who fit in each room, in ascending order like this:

enter image description here

Then comes the hard part. In the list, I want to specify which cells we can use, so I do this by using the OFFSET() function. It looks like OFFSET(reference, rows, cols, [height], [width]) so the base code for showing the whole list would be OFFSET(A2, 0, 0, 20, 1) ie, A2 and 20 rows down.

So far so simple, but we need to match is with the people as well.

Match syntax is MATCH(lookup_value, lookup_array, [match_type]) so the base would be like MATCH(C2, B2:B21, 1)

The MATCH function will only return a number, the row number of the room with that amount of people. So we want to change the offset by that number, to skip previous rooms and we also want to reduce the list range with the same number, so we don't just move the whole thing further down.

Just treat it like a number and put it in the function like so:

OFFSET(A2, MATCH(C2, B2:B21, 1), 0, 20-MATCH(C2, B2:B21, 1), 1)

This will offset the list down, and shrink it.

I ran into several problems using this approach, and I'll summarize:

When #people is an exact match to a room, it will return the last row of a room that size. We don't want this, as we can still use that room. Solution? A minus one to the value of C2, or change the list to be "number of people who can't fit the room", which is a bit silly.

When less than 3* people, there will be no room to return (4 because we added a minus one), so I have to add an IFERROR check in the code.

End result is this:

=OFFSET(A2,IFERROR(MATCH(C2-1,B2:B21,1),0),0,20-IFERROR(MATCH(C2-1,B2:B21,1),0),1)

It's large, but it will fit in the box no matter how many rooms of different sizes.

With the list looking like in the last example image, this is the only code you need for the list. Good luck!

  • Hey thanks for the advice. My issue now is that using IF statements, or nested if statements works fine for only a few rows but if you have a lot of criteria it gets so long that I can't paste the it into the formula box:=IF($E$96<=3,Sheet1!$A$2:$A$22, IF($E$96<=6, Sheet1!$A$3:$A$22, IF($E$96<= 8, Sheet1!$A$5:$A$22, IF($E$96<= 9, Sheet1!$A$6:$A$22, IF($E$96<=10, Sheet1!$A$7:$A$22, IF($E$96<=15, Sheet1!$A$8:$A$22, IF($E$96<=16,Sheet1!$A$10:$A$22, IF($E$96<=18,Sheet1!$A$11:$A$22, IF($E$96<=20,Sheet1!$A$14:$A$22, IF($E$96<=34,Sheet1!$A$17:$A$22, IF($E$96<=100,Sheet1!$A$18:$A$22,))))))))))) – tfantina Nov 2 '18 at 12:04
  • Added an alternative to IF (IFS), and a workaround for the small formula box. – Christofer Weber Nov 2 '18 at 16:36
  • Added a third chapter of more stuff you could potentially use. If you for some reason need to keep all the code in the list. – Christofer Weber Nov 2 '18 at 17:48
  • 1
    Perfect, that third part is awesome! Thanks. – tfantina Nov 5 '18 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.