Formula Help
I have column with tittle Cost Center (CC), I need to create a formula to pick up 8 cost centers ID and flags in another column. The idea is to identify these 8 values as they need to be modify prior to another process taking place.
I have tried the HAS and IF(CONTAINS) but unsuccessful in string -
For example: IF(CONTAINS("CC1" "CC2" "CC3", [Cost Center (CC): [Cost Center (CC)]), "Update EC", "Don't Update EC")
In contains can it search for more than one value at a time?
Any guidance is appreciate it!
Best Answer
-
That is correct, and in that case, it would output "Update EC".
I just noticed as well that your last formula that you posted is missing a quote after "Update EC".
Answers
-
Are you able to provide some screenshots for reference?
-
Hi Paul,
Yes, of course. Here are some screenshots -
This is my column (right now, I have 650 rows, new entries get added weekly)
From Cost Center (CC) column - i need to identify the eight values below:
The idea is to flag or identify entries with CC that match these eight values.
-
Is this going on every row to flag only those rows that contain one of those 8 values in the second screenshot, or are you trying to evaluate the entire column as a whole and if even once cell has any of those 8 values, you want the output to be "Update EC"?
-
Hi,
The latter; trying to evaluate the entire column as whole and if even one cell contains any of those 8 values, I want the output to be "Update EC".
Thank you!
-
In that case you will need multiple CONTAINS functions nested in an OR like so:
=IF(OR(CONTAINS("This", [Column Name]:[Column Name]), CONTAINS("That", [Column Name]:[Column Name]), CONTAINS("Something Else", [Column Name]:[Column Name])), "do this", "otherwise this")
-
Hi Paul,
I entered this:
=IF(OR(CONTAINS(“CC00963”, [Cost Center CC]:[Cost Center CC]), CONTAINS(“CC01499”, [Cost Center CC]:[Cost Center CC]), CONTAINS(“CC01500”, [Cost Center CC]:[Cost Center CC]), CONTAINS(“CC01501”, [Cost Center CC]:[Cost Center CC]), CONTAINS(“CC01502”, [Cost Center CC]:[Cost Center CC]), CONTAINS(“CC01503”, [Cost Center CC]:[Cost Center CC]), CONTAINS(“CC01506”, [Cost Center CC]:[Cost Center CC]), CONTAINS(“CC01602”, [Cost Center CC]:[Cost Center CC])), “Update EC”, “Don’t Update EC”)
Returning #UNPARSEABLE. Is there a limit on the OR function?
-
The issue now is with your quotes. See how yours are slanted but mine were straight up and down? The slanted ones are called "Smart Quotes" which (ironically enough) aren't valid characters in a Smartsheet formula. Try retying the formula directly in the sheet or in a text editor such as Notepad (not Word).
-
Hi Paul,
Using notepad worked! But now I am getting a true value when I should be a false. I am tried both just the CCNumber and CCNumber and description still receive a true "Update EC" when I should be getting "Don't Update EC"
Any reason for this?
-
Are you able to provide some screenshots for context?
-
Yes,
True value - when it should be "Don't Update EC"
"CC01357" is not one of my original 8 values - so it should give me "Don't Update EC"
Formula:
=IF(OR(CONTAINS("CC00963", [Cost Center CC]:[Cost Center CC]), CONTAINS("CC01499", [Cost Center CC]:[Cost Center CC]), CONTAINS("CC01499", [Cost Center CC]:[Cost Center CC]), CONTAINS("CC01501", [Cost Center CC]:[Cost Center CC]), CONTAINS("CC01502", [Cost Center CC]:[Cost Center CC]), CONTAINS("CC01503", [Cost Center CC]:[Cost Center CC]), CONTAINS("CC01506", [Cost Center CC]:[Cost Center CC]), CONTAINS("CC01602", [Cost Center CC]:[Cost Center CC])),"Update EC, "Don't Update EC")
-
Have you applied a filter to the source data or used the ctrl+f to search the sheet?
-
I have used a filter on the source data, but there are no active filters on it now. I have not used the CTRL+F to search sheet
-
the Contains formula should pick up "CC01501" in a cell with this value "CC01501 UMMG - Ambulatory Surgical Center - Palm Beach Gardens" correct?
-
That is correct, and in that case, it would output "Update EC".
I just noticed as well that your last formula that you posted is missing a quote after "Update EC".
-
Hi Paul!
yes, that quote was a typing error. I was referencing the entire column [Cost Center CC]:[Cost Center CC], and it is true the value exists, so I updated to reference the row in the cell [Cost Center CC]@row and it works!
Thank you so much for all your assistance with the CONTAINS Formula@ Truly appreciate it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!