Apple Event: May 7th at 7 am PT
I am trying to create a sort of sign in page for guests at a function.
Part of the page is a drop down list where the guests have to choose a time to visit a certain location. Only 10 people can be accommodated per time slot. So the question I have is if it is possible to incorporate a if function into the drop-down list? As soon as time slot A has 10 people, it is removed from the list?
OS X Mavericks (10.9.1)
Hi t,
Short anwer: No.
Pop-up menus in Numbers are static. Their contents may be edited, but this requires manual intervention.
You could display the list of time slots separately, and use COUNTIF to display the number registered (or the available spaces), and conditional formatting to colour the cell when the slot is full. It might also be possible to use conditional formatting on the cell itself to provide a warning that a choice was not available, but this would go into effect only after the choice had been made, not on the pop-up's item list.
Regards,
Barry
Hi t,
Short anwer: No.
Pop-up menus in Numbers are static. Their contents may be edited, but this requires manual intervention.
You could display the list of time slots separately, and use COUNTIF to display the number registered (or the available spaces), and conditional formatting to colour the cell when the slot is full. It might also be possible to use conditional formatting on the cell itself to provide a warning that a choice was not available, but this would go into effect only after the choice had been made, not on the pop-up's item list.
Regards,
Barry
Thanks.
So a new question. I create separate columns for the different time slots. If 10 people tick the box for time A, can I then somehow hide that entire column? Or maybe just color it red?
You can't hide the column using a formula, but you might be able to colour it or give the appearance of its being coloured using conditional formatting.
Here's a sample, constructed in Numbers 2.3. There's an exploded view immediately below, followed by the 'in use' view below that:
Appearance in use:
The second table has been slid behind the first. The first has its fill set to 'none'. Note that the count shows through. This can be avoided, if desired, by setting the default text colour on table 2 to white, and the conditional text coloour to match the conditional fill colour.
Regards,
Barry
In Numbers 3 it is easier to do conditional formatting. Here is another idea:
I set it to turn red when a slot has three or more checkboxes checked.
Formula in B7 (footer row) =IF(COUNTIF(B,TRUE)≥3,FALSE,"")
Formula in B8 (footer row) =IF(COUNTIF(B,TRUE)≥3,TRUE,"")
Drag-Fill those formulas to the other columns
Select all the checkboxes inColumn B and set up the conditional formatting rule
Drag-Fill to the other columns.
Hide the two footer rows.
There is probably another way to do this that requires only one footer row. It hasn't struck me yet, though.
A different idea. This one turns yellow when a slot is full and red when overfilled. Note that the colors are on the checked boxes only.
B8 =IF(COUNTIF(B,TRUE)=3,TRUE,"")
B9 =IF(COUNTIF(B,TRUE)>3,TRUE,"")
With two pairs of trigger rows, Badunit's two solutions could be combined to colour the whole column yellow when the slot is full, red when overfull.
Two formulas are similar to those used in Badunit's examples:
B11: =IF(COUNTIF(B$2:B$10,TRUE)<>3,"",IF(COUNTIF(B$2:B$10,TRUE)=3,TRUE,FALSE))
B13: =IF(COUNTIF(B$2:B$10,TRUE)>3,TRUE,"")
The formula below is used in B12 and B14 to provide the FALSE value to pair with TRUE or a 'blank' to pair with a 'blank':
B12: =IF(B11=TRUE,FALSE,"")
B14: =IF(B13=TRUE,FALSE,"")
Conditional formatting rules were set as shown. Constructed in Numbers '09 v2.3, but should work (and be easier to construct) in Numbers 3.
Regards,
Barry
Numbers drop down list and if statement
