Google Sheets Drop Down Menus that remove used options

Question

I am attempting to use Google Sheets as a scheduler of sorts for some rooms. A1:A4 is a list of room names.

B1:B4 is using this formula =IF(COUNTIF($H$4:$H$6,A1)>=1,"",ROW()) Where the H values are where I want the dropdown to limit if a selection is used.

C1:C4 is using this formula that should remove the options used from the next section of the drop down. =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$4),"",INDEX(A$1:A$4,SMALL(B$1:B$4,1+ROW(A1)-ROW(A$1))))

This section is working as desired, though when I attempt to use the formula in another section that is not A1, B1, C1, etc I am getting the parameter is out of range.

I am not super savvy in this but attempting to learn.

How would I use the above formulas in any section of the spreadsheet with out this error?

Here is the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1WT_Um6ro1djhKxCdjWqrbXW9tY1E7E4DUzyE58GG_Uw/edit?usp=sharing

Thanks in advanced. Nick

Answer 1

CONTEXT

You are using Data validation to make "Room" selections for time periods on a given day. You want to remove existing selections from the range of available "Rooms". The original question has been answered - the solution related to adapting a "helper column" formula to new time periods on a new day.

However, the solution highlighted flaws/weaknesses in the existing methodology.

1. Inconsistent layout of time periods for day-to-day and week-to-week.
2. Each and every time period requires its own helper column.

---

METHOD

The following attempts to offer an approach that overcomes these flaws and will also enable creation of sheets for new months that retain Data validation settings and simplify the work required to configure the new month.

Assumptions:

• Each sheet applies to a calendar month. Data is broken down into:
  • Weeks (by column), and
  • Days (by row range)
• Each week of the month occupies a given number of columns.
  • Week one is first, week two is adjacent to the right of week one, and so on
• A week contains data for seven days
  • The first rows apply to the first day - Sunday, then (proceeding down the column) Monday, Tuesday and so on until Saturday.
• Room selections are made each dDay
  • On Sunday and Friday, there is one one time period
  • On days other than Sunday and Friday, there are three time periods.
• Sheets apply to a Calendar months.
  • Day#1 of Week#1 is not necessarily the first day of the given month

Sheet Layout

• Standardise and simplify the layout. For example,
• Delete existing Columns A, B and C. Helper columns are included after the weekly columns (refer below)
• Only one row of Headers.
• This makes it easy to delete existing information (manually or by script) to create a new month.
• No unnecessary rows at the bottom of sheets
• Dates:
  • Week#1: Manually insert the start date in the appropriate day in Column B
  • Week#2+: Date fields are formula.

---

Sample layout

---

FORMULA

• "Capacity" and "Price"

  • Cell I4:
    • =arrayformula(iferror(vlookup(H4:H,'Cell Reference'!$A$2:$C$5,{2,3},0),))
    • this formula populates Column values for Capacity and Price, and also automatically expands down the sheet.

• "Availability"

  • The existing formula in this column is unclear. I expect that it could be improved but, due to lack of clarity it has not been changed.

• "Dynamic values in drop downs"

  • There must be a unique "helper column" for each time slot on each day, in each week, in each month.
  • Formula for Helper columns can be simplified by using:
    • variables for the input column, and the start and end rows
    • ADDRESS and INDIRECT to access variable values.
    • A typical helper formula looks like this:

=query(query({'Cell Reference'!$A$2:$A; indirect(address(BE$5,$BE$4,4,1)&":"&address(BE$6,$BE$4,4,1),1)},"Select Col1, count(Col1) where Col1<>'' group by Col1"),"Select Col1 where Col2=1")

Formula Logic

• Cell Reference'!$A$2:$A - the list of rooms
• address(BE$5,$BE$4,4,1)&":"&address(BE$6,$BE$4,4,1):
  • BE$5 - first row in the time slot range
  • BE$6 - last row in the time slot range
  • $BE$4 - "Room" Column
  • '&":"&` - joins the two address formula to create a dynamic range.
• indirect(address(BE$5,$BE$4,4,1)&":"&address(BE$6,$BE$4,4,1),1)
  • delivers the data for the dynamic range.
• The select clause displays values where the count = 1 (i.e. disregards any values that are in the existing dropdown timeslot range.

DATA VALIDATION

• Each time slot on each day has a corresponding helper range for dropdowns. These need to be assigned manually.
• Data validation will be retained if/when the sheet is duplicated (as for a template).

---

Helper Columns

---