MAX COLLECT for blank cells

Katie I.

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!

Nic Larsen

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))