How does COLLECT() with String Criterion Work?

Nicholas Mathern

I have a simple formula (I thought) that seems to broken. It seems to be centered around the existence of a String in the Criterion Parameter of COLLECT().

Formula: =IF(MIN(COLLECT(Date:Date, [Work Type]:[Work Type], "Mandatory"))=Date1, Hours1-6, Hours1)

Result: #UNPARSEABLE

Is it not possible to have a string Criterion like "Mandatory" in COLLECT()?

SoS | Dan Palenchar

Are you using the correct column headers? The formula works for me, to the degree that it parses and returns a value. There is no issue putting a string in text so long as it is surrounded by quotes as you've done.

I'll add it's not fully clear what you are trying to accomplish with this formula. If you can explain the desired functionality I could potentially give a more useful response.

SoS | Dan Palenchar

Are you using the correct column headers? The formula works for me, to the degree that it parses and returns a value. There is no issue putting a string in text so long as it is surrounded by quotes as you've done.

I'll add it's not fully clear what you are trying to accomplish with this formula. If you can explain the desired functionality I could potentially give a more useful response.

Nicholas Mathern

https://community.smartsheet.com/discussion/comment/261070#Comment_261070

Dan,

I've spent years of my life being careful with formulas like this making sure every character is precisely what it should be... and yet, that must have been my problem here.

I promise I tried re-typing everything several times in several different simpler versions before I came here. Perhaps it was a passing bug or I just needed to take a walk outside? Hah!

Thanks for convincing me it was worth another try!

Regards,

Nick M.

SoS | Dan Palenchar

@Nicholas Mathern it happens to the best of us! Happy to help.