How can I do something like IF COLUMN CONTAINS A VALUE???

Jeremy Gordon

I have a Project Plan (PP) sheet and Project Metadata (PM) sheet.

From the PM sheet, I want to search through the entire "Status" column in the PP sheet to see if any cell contains "On Hold". I've already created the reference from PM to the PP Status column and named it {PP - Status}.

I'd expect this to work, but I can't get it to work:

=IF(CONTAINS("On Hold", {PP - Status}, "On Hold", "Not On Hold"))

I did find a formula snippet in another post, but I don't even understand how it works. Any explanation is welcome! The part I don't understand in the formula below is the "CONTAINS("On Hold", @cell)" part... specifically, what the heck is @cell doing?

=IF(IFERROR(INDEX(COLLECT({PP - Status}, {PP - Status}, CONTAINS("On Hold", @cell)), 1), "") = "On Hold", "On Hold", "Not On Hold")

Also, I had to at the IFERROR part since I get "INVALID VALUE" if there isn't a match. I'm not clear why about this either.

What's the simplest formula to achieve my goal? Also, can anyone help clear up confusion on the @cell thing I mentioned above?

Ipshita

Hello @Jeremy Gordon

To answer your question, the @cell argument performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range, making your formula more efficient. Basically, you just select a cell to reference it and work with that cell's data in your formula. (You can also manually type the column name and row number to reference the cell.)

Adding the IFERROR part is always a good idea because if there is no value to match your request, then IFERROR will return a blank cell value instead of #INVALID because it may be so that in the PP sheet, one or more cells do not have either the On Hold or Not On Hold Value, in that case your IFERROR will return a blank cell value in your PM sheet for that cell reference and the moment a value is added in the PP sheet for that cell, your formula would return the same value.

Now, to explain the INDEX(COLLECT) function, the formula above has used the COLLECT function inside of the INDEX function to return an item from a range based on the collected values that meet the specified criteria. That is exactly what you need in this case.

Here's a little more about the INDEX function -

https://help.smartsheet.com/function/index#:~:text=You%20can%20use%20the%20COLLECT,that%20meet%20the%20specified%20criteria.

Hope this helps!

Cheers,

Ipshita