Formula Help

DioG

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!

Paul Newcome

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".

Paul Newcome

Are you able to provide some screenshots for reference?

DioG

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.

Paul Newcome

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"?

DioG

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!

Paul Newcome

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")

DioG

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?

Paul Newcome

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).

DioG

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?

Paul Newcome

Are you able to provide some screenshots for context?

DioG

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")

Paul Newcome

Have you applied a filter to the source data or used the ctrl+f to search the sheet?

DioG

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

DioG

the Contains formula should pick up "CC01501" in a cell with this value "CC01501 UMMG - Ambulatory Surgical Center - Palm Beach Gardens" correct?

Paul Newcome

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".

DioG

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!