Properly using Max(collect)
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.
Best Answer
-
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?
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!