MAX COLLECT for blank cells
Hello all, we use a Smartsheet form for students in our internship program to submit their weekly hours worked for our instructors to track. Because the form creates a new row for each submission, I created a rollup sheet using MAX(COLLECT() to show all submissions on a single row to make it easier for our instructors. I've been using this formula, which is working great:
=MAX(COLLECT({Week 1}, {Time Log ID}, ID@row), 0)
It returns a 0 if the student did not submit anything for that week, but there may be weeks when students do not work and enter 0 on their form. I'm looking for a way for it to say "No Submission" if there is no value in the cell so instructors do not have to double check. I've scoured the formus for a solution and have tried using these formulas, but they all return as #UNPARSEABLE:
=IF(HAS({Week 1}, {Week 1}@row), MAX(COLLECT({Week 1}, {Time Log ID}, ID@row), "No Submission")
=IF({Week 1}@row <> "", MAX(COLLECT({Week 1}, {Time Log ID}, ID@row), "No Submission"
=IF(ISBLANK({Week 1}@row), "No Submission", MAX(COLLECT({Week 1}, {Time Log ID}, ID@row)
Here is a screenshot of the Time Log sheet with some test entries:
And the rollup sheet:
Any advice would be appreciated! Thank you!
Answers
-
I was able to get your third option to work with a slight adjustment:
=IF(ISBLANK({Week 1}@row), "No Submission", MAX(COLLECT({Week 1}, {Time Log ID}, ID@row), 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!