0

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

5
  • Hi. This is a quick fix: in cell B11, try =IF(COUNTIF($H$4:$H$6,A11)>=1,"",ROW()-10). This will work but I think it gives a false sense of success because there seem to be inconsistencies in your formula. For example, Cell B11 - why does this formula reference the range "$H$4:$H$6" (the range for Day#1)? Shouldn't the reference be H11:H19?
    – Tedinoz
    Oct 16 at 7:07
  • Oh you are correct! Thanks for pointing that out. That is working on getting the value into the range which is now stopping the error. Although When I can change this to the next section, I can still select a value that is previously used.
    – Nick
    Oct 16 at 17:06
  • "When I can change this to the next section, I can still select a value that is previously used". This is because the next section (H11:H19) is actually three adjacent ranges - (H11:H13, H14:H16 and H17:H19) whereas you have but one helper range. In fact, you need three helper ranges for each day.
    – Tedinoz
    Oct 17 at 1:10
  • You need to think differently about the helper columns. Read Distinct Values in Drop-Down List in Google Sheets. I think each sheet needs it own helper columns (out past column AL). This can be automated to an extent by using start and stop row numbers, and using indirect and address to create dynamic ranges. I've prototyped this, and it works well but I haven't yet addressed the changing day ranges in each week. Ideally, you want a "month template" and I think this should be your goal.
    – Tedinoz
    Oct 17 at 2:20
  • This is a great link! Thank you! Still attempting to learn all of this. I appreciate the knowledge direction.
    – Nick
    Oct 18 at 23:12
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

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

Helper Columns


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.