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