# Properly using Max(collect)

edited 07/27/21

I am trying to get Column37 to output the value in the "Is the employee currently off" column.

I don't understand why this is not finding a match.

I have done this exact same thing in another sheet and it works perfectly but for some reason, this is not matching

Here is the other one, which works for some reason.

Update:

Somehow I solved it by adding and referencing a worker column that was =IF([is the employee currently off?]@row = 1, 2, 1).

I still don't understand why this change worked - would you be able to help me understand this Paul?

• ✭✭✭✭✭✭
edited 07/27/21

How is the data in the "Is the employee currently off" column being populated?

You have the #NO MATCH error in the first column. Getting rid of that error should clear up the error in your MAX/COLLECT.

• Hi Paul,

This cleared up the error in my MAX/COLLECT but ran me into another issue.

Why is my max(collect) function not returning 1 for cases where [Is the employee currently off?] is 1?

Since [Is the employee currently off?] can only be 1, shouldn't column37 output a 1 whenever [Is the employee currently off?] is 1

Update:

Somehow I solved it by adding and referencing a worker column that was =IF([is the employee currently off?]@row = 1, 2, 1).

I still don't understand why this change worked - would you be able to help me understand this Paul?

• ✭✭✭✭✭✭

Exactly how is the data populated in the [Is the employee currently off?] column?

• [Is the employee currently off?] is populated by comparing today's date to the employee's requested time off window: =IF(AND([Start Date]@row < TODAY(), TODAY() < [End Date]@row), "1", "0").

Here, [start date] is representative of the first day being taken off and [End date] is representative of the last day being taken off.

• ✭✭✭✭✭✭

Try removing the quotes from around those numbers. The quotes are outputting a text value. Without the quotes it outputs a numerical value (which is why the reference formula in the helper column worked).

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!