IF(count) function
I have a smartsheet that is performing a lookup on another smartsheet to see if a value exists. It doesn't seem to be working though because it always comes back as false. Any ideas what I might have done wrong?
Smartsheet 1:
=IF(CONTAINS([Cost Center]@row, {RML Pulse Cost Accounting Range 1}), "Green", "Red")
[Cost Center]@row = 7201770551
Range {RML Pulse Cost Accounting Range 1} = Entire column 1 on other smartsheet.
Column 1 in other smartsheet contains 232 rows and 1 row equals 7201770551
It always returns red though and never green. I tried setting the range equal to just a single cell that contained the 7201770551 value but that didn't work either.
Any suggestions?
Answers
-
I found a way around this by converting my lookup column 1 on the other smartsheet to have =7201770551 + "" which I guess converts it to text. I don't understand why they would not allow the function to find numeric data. This fixes my issue, but if there is a better way to achieve this outcome, I would be interested to know for future use.
-
Hey Adam
Your original formula will work with a slight tweak
=IF(CONTAINS([Cost Center]@row, {RML Pulse Cost Accounting Range 1})="true", "Green", "Red")
As a note, the CONTAINS function doesn't look for an exact match. In your example of
[Cost Center]@row = 7201770551
a true would also be returned in values that had extra digits at the beginning and/or at the end of the the value, ex 1117201770551999. If this is a concern, the HAS function looks for exact matches.
cheers
-
I tried your suggestion but same result, it always goes to Red. I think the problem is that the contains function cannot evaluate a numeric field. I found another user stating this.
-
Sorry, I think you're right. Yesterday I must have had a space in the field - I can't replicate it today but I easily toggled between red and green yesterday. Today I had to add a letter to the number for it to pick it up.
This formula does work on my sheet. If the COLLECT finds anything, then the count being greater than zero is true.
=IF(COUNT(COLLECT({RML Pulse Cost Accounting Range 1}, {RML Pulse Cost Accounting Range 1},[Cost Center]@row = @cell)) > 0, "Green", "Red")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!