Recieving a "Expecting Contact" Error despite both Columns being Contact Columns

George Abbott

I am trying to make a spreadsheet that connects the most recent grass cutting in a certain area to the contractors that cut the grass based on data from another sheet. I have figured out how to log the day the mowing takes place, and when the next mowing needs to occur, but I am having a hard time marking who most recently mowed an area.

My current set up is:

=IF(CONTAINS("Ashburn Coppock Park", {Grass Cutting & Landscaping Form Range 1}), COLLECT({Grass Cutting & Landscaping Form Range 3}, {Grass Cutting & Landscaping Form Range 2}, MAX({Grass Cutting & Landscaping Form Range 2})))

-- Ashburn Coppock is the area that needs to be mowed

-- Grass Cutting & Landscaping Form Range 1 is the list of areas that need to be mowed

--Grass Cutting & Landscaping Form Range 2 is the list of dates reported

--Grass Cutting & Landscaping Form Range 3 is the list of contractors.

Any and all help would be appreciated! Thanks!

Genevieve P.

Hi @George Abbott

The COLLECT function acts as a filter to create a range, but it needs to be within another function. I would suggest using INDEX(COLLECT in this instance, for example:

=INDEX(COLLECT({Grass Cutting & Landscaping Form Range 3}, {Grass Cutting & Landscaping Form Range 2}, MAX({Grass Cutting & Landscaping Form Range 2}, {Grass Cutting & Landscaping Form Range 1}, CONTAINS("Ashburn Coppock Park", @cell)), 1)

This adds in your criteria of "Ashburn Coppock Park" into the COLLECT filter, so it will only check through rows that contain that value in your Range 1 column.

The 1 at the end of the INDEX function identifies that you want to bring the first matching row back, in case there are duplicates.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Genevieve P.

Hi @George Abbott

The COLLECT function acts as a filter to create a range, but it needs to be within another function. I would suggest using INDEX(COLLECT in this instance, for example:

=INDEX(COLLECT({Grass Cutting & Landscaping Form Range 3}, {Grass Cutting & Landscaping Form Range 2}, MAX({Grass Cutting & Landscaping Form Range 2}, {Grass Cutting & Landscaping Form Range 1}, CONTAINS("Ashburn Coppock Park", @cell)), 1)

This adds in your criteria of "Ashburn Coppock Park" into the COLLECT filter, so it will only check through rows that contain that value in your Range 1 column.

The 1 at the end of the INDEX function identifies that you want to bring the first matching row back, in case there are duplicates.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

George Abbott

Howdy @Genevieve P.,

That worked after a little bit of tweaking! Thank you so much!

Since posting, I have encountered another error with how I am collecting dates. The formula I am using as of now collects the most recently date an area was mowed on it's own, i.e. if "Yacht Yard" was mowed on the 17th of May and recorded as being mowed in the fillable form on its own it will return 05/17/22, however the latest date it was mowed was the 24th of May but when the form was being filled out, several other locations were checked off too - I will include screenshots to illustrate what I am talking about.

My current formula is:

=IF(CONTAINS("Yacht Yard", {Location}), MAX(COLLECT({Date}, {Location}, "Yacht Yard")))

Swapping in HAS for CONTAINS yields a blank box, not even the incorrect date.

Thank you again for your help!

Genevieve P.

Hi @George Abbott

I'm glad you were able to get it to work!

In regards to your current formula, I would definitely recommend using the HAS Function instead, within your COLLECT function. I would also personally use a COUNTIF as your first statement, to see if the COUNT of rows that have "Yacht Yard" selected is greater than 0.

E.g:

=IF(COUNTIF({Location}, HAS(@cell, "Yacht Yard")) >0, MAX(COLLECT({Date}, {Location}, HAS(@cell, "Yacht Yard"))))

Cheers,

Genevieve