CONTAINS formula always returns no match

Vlad G · 05/05/22

I have a Row Counter column created with this formula: =COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> "")) . It is designed to house row numbers so I can reference that number in another formula.

I have the column Is Predecessor created with formula =IF(CONTAINS([Row Counter]@row, Predecessors:Predecessors), "Yes", "No").

Its purpose is to check whether the row is used as a predecessor for any tasks in the Predecessors column. However, it always returns No even for the rows which numbers are definitely used as dependencies in Predecessors column. What is the issue here?

Kevin Smith · 05/06/22

Hi Vlad,

I apologize for not knowing this sooner, but I think this is what you are looking for.

I have never used it but it's the function SUCCESSORS.

Try this:

=JOIN(SUCCESSORS([Task Name]@row), " , ")

This should give you what you want, Not sure why it is a function but it's really nice to have it there.

Good luck, let me know if it doesn't work.

Jeff Reisman · 05/05/22

@Vlad G

Do you see how your values in the Predecessors column are on the left side of the column, and the values in the Row Counter column are on the right side of the column? That's because the Predecessors values are numbers stored as text, and the Row Counter values are numbers stored as numbers. A text value will never match a number value in Smartsheet.

So how do you get around this? You either need to change the way your Predecessor values are being created, or you have to make your formula believe it's comparing text to text. Since I don't know how you are creating the Predecessor value, we'll go with changing the IsPredecessor formula:

=IF(CONTAINS([Row Counter]@row +"", Predecessors:Predecessors), "Yes", "No")

That's it. That's all you gotta do! Adding the +"" (two double quotes) to the [Row Counter]@row value makes Smartsheet convert the number value to number stored as text, and therefore it can match the Predecessor value.

Kevin Smith · 05/05/22

Hi Vlad

I have found this confusing as well but Predecessors like some other fields are special fields. So by the way it's aligned you would think it's a text field but using VALUE on it doesn't work, so what will work is if you use a helper field for something like Predecssor (no 's') and use this formula in it : =Predecessors@row

Then do your CONTAINS to look at the =IF(CONTAINS([Row Counter]@row, [Predecessor]:[Predecessor]), "true", "false")

You should get a col. with what you are looking for.

I hope that helps.

Kevin Smith · 05/05/22

My Apologies, did not mean to step on Jeff's answer. I wasn't as quick but certainly want to concede to his answer as a valid option as well.

Vlad G · 05/05/22

@Jeff Reisman That would make sense but for some reason that update to the formula didn't change the results. The Predecessors column is set to Predecessors column type by Smartsheet as that column is selected as such in the project settings. I don't know what kind of data type is set to that column type but I simply input the numbers of rows into that column along with dependency types indicators like FF sometimes. On row 5 I have 5 in the Predecessors column and 5 in the Row Counter column

I did a little experiment and it looks like that conversion to text needs to happen on Predecessors column as well. But I don't know how to indicate that for the whole column in the formula. Predecessors:Predecessors + "" is not tracking. Any idea how to do that?

Vlad G · 05/05/22

https://community.smartsheet.com/discussion/comment/328511#Comment_328511

Thank you, that made some progress! However, now the issue seems to be that I am not getting a reliable value as it is checking no an exact match. So it's telling me Yes for row 1 because Predecessor column has such values as 12, for example. But it should show No, because no cells have value 1 by itself. It doesn't look like this is going to work either as some values will be combined with dependencies types like FF, FS, etc. Doesn't look like it's possible to achieve a simple task of wanting to know if the row is used as predecessor on any other row... :/

Kevin Smith · 05/05/22

I am not getting that same issue.

Here is my test:

Please send me your formula if you are still having issues.

Kevin Smith · 05/05/22

Sorry, I am getting that problem. Ha, missed that. Let me give that some thought.

Kevin Smith · 05/05/22

As you can see, it's as you explain it, the character "1", "2", "5" are getting picked up and showing true, because they exist in both Row Ctr and Pred-dup. My apologies. This will not work. I need to give this more thought, hoping someone else has encountered this challenge. If we could get both to numbers that might work but, I am not sure how the multiple Predecessors would be dealt with.

Jeff Reisman · 05/05/22

@Vlad G Ah - didn't realize it was a Project sheet. I know those have special rules but I haven't dealt with them enough to know what they are.

With @Kevin Smith 's method of isolating the Predecessor values, we can look for exact matches instead:

=IF(COUNTIF([Predecessor]:[Predecessor], [Row Counter]@row) > 0 , "true", "false")

This formula looks in your new Predecessor column, and counts any rows where it finds the Row Counter value for that row. If that count is greater than 0, that's the true condition.

Kevin Smith · 05/05/22

Many Thanks Jeff. I have been stumped by this. I could not get the COUNTIF to work (probably my impatience) but you got me thinking and I was able to get something to work for me.

I want to identify when a row counter values shows up in a range. THIS ONLY WORKS FOR WHEN THERE IS A SINGLE PREDECESSOR. Sorry for the caps but I need help figuring out how to parse multiple predecessors.

I am using this formula:

=COUNTIF([Predecessor]:[Predecessor], HAS(@cell, [Row Counter]@row))

Now this gives a number in the col. So if it has a value '>' 0, it will be a predecessor. However, as I yelled, it doesn't work if there is more than one.

You could wrap the COUNTIF inside an IF and get "Yes" "No" or something else.

If you have to figure out multiples, it will take some work but perhaps someone can help with that. This might help with that, you can get an error on multiple predecessors if you use VALUE, it will return #INVALID VALUE.

Sorry about the earlier confusion.

Vlad G · 05/06/22

https://community.smartsheet.com/discussion/comment/328531#Comment_328531

That doesn't work, I suspect because there still has to be conversion to the same data type happen within the query. I just don't know how to do that for the whole column part. Like [Predecessor]:[Predecessor] + "" or something like that.

Another challenge with this query is that Predecessors column can feature multiple dependencies like 12, 15, 55FS, etc. This honestly feels like a feature that should be in the SS since it deals with the specific feature of dependencies.

Kevin Smith · 05/06/22

Hi Vlad,

I apologize for not knowing this sooner, but I think this is what you are looking for.

I have never used it but it's the function SUCCESSORS.

Try this:

=JOIN(SUCCESSORS([Task Name]@row), " , ")

This should give you what you want, Not sure why it is a function but it's really nice to have it there.

Good luck, let me know if it doesn't work.

Kevin Smith · 05/06/22

Forgot you were looking for a true/false. If you still just need yes or no, just look through the col. you set for Successors and if it's not blank, it's a Yes.

Cheers.

Vlad G · 05/09/22

https://community.smartsheet.com/discussion/comment/328756#Comment_328756

Yesss, this is it, thank you so much! :) Funny that I couldn't find this info anywhere, you'd think successors would be mentioned as a reference somewhere for predecessors. My search queries must have been a bit too convoluted.

CONTAINS formula always returns no match

Best Answer

Answers

Help Article Resources

Categories