IF used in MAX COLLECT?

Hall

Hi

Our Jobs done sheet is populated through a form to give.

We must make 2 visits contracted visits a month. However, sometimes we are called out for a repair.

I have a sheet that calculates the days since we carried out a site visit.

Date attended =MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1))

range 2 = Date. Range 1 = Name

However, I only want it to return the dates of the 'contract' and not repair as John should be 5 days since done.

Is it possible to return the MAX date IF job type = 'Contract'?

Thanks

Hall

Christian G.

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1,{Job type},"Contract"))

Paul Newcome

@Hall

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1, new range, new criteria))

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1, {Job Type Column}, @cell = "Contract"))

Paul Newcome

@Hall Once you pull the date into your sheet you can use an INDEX/COLLECT.

=INDEX(COLLECT({Text Range}, {Date Range}, @cell = [Most Recent Date Column]@row, {Job Type}, @cell = "Contract"), 1)

Paul Newcome

You would include another range/criteria set in the COLLECT function to look at the type and specify "Contract" for the criteria.

Hall

@Paul Newcome

Thanks. How would I do that? I am a noob for Smartsheet.

Christian G.

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1,{Job type},"Contract"))

Paul Newcome

@Hall

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1, new range, new criteria))

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1, {Job Type Column}, @cell = "Contract"))

Hall

Hi Guys.

Thanks a lot. It works. I hadn't realised that you could add conditions into the COLLECT.

Cheers

Hall

Paul Newcome

@Hall Yes. As long as your syntax and whatnot are proper, you can go out to a maximum of 4,000 characters (including spaces) for the full formula.

Hall

@Paul Newcome. Now you've got me thinking. How would I return the value in the Forman Column so I know who attended on the most recent visit? The formula is looking for a max date but I require a text. Not sure if this is the source of my error.

Thanks

Hall

Paul Newcome

@Hall Once you pull the date into your sheet you can use an INDEX/COLLECT.

=INDEX(COLLECT({Text Range}, {Date Range}, @cell = [Most Recent Date Column]@row, {Job Type}, @cell = "Contract"), 1)

Hall

@Paul Newcome

Thanks. That's worked great.

Until the next time, cheers.

Hall

Paul Newcome

Happy to help. 👍️

Hall

@Paul Newcome Everything is working great and I have expanded it too.

It now brings data that has highlighted a second issue. We have to make 2 site visits per month, however, some customers require those dates on their invoices. How do I return a second date prior to the MAX date?

Thanks

Hall

Paul Newcome

@Hall You would use the LARGE function instead of the MAX function.

LARGE(range, 1) will pull the largest value (or most MAX).

LARGE(range, 2) will pull the second largest.

Hall

@Paul Newcome

Cheers. That works. Thanks

I am going to have to find something more challenging for you to solve!

Hall

Paul Newcome

@Hall Happy to help. 👍️