Conditional Formatting with Text Validation from List

bkg73123
bkg73123 ✭
in Formulas and Functions

I have a column that has different names in it [HANGERS].

I have a master list of names at the bottom of the sheet in a different column just floating at the bottom there out of the way. I do not want a dedicated column for this list if it isn't necessary. Range [BUILDING]623:[BUILDING]648.

I want conditional formatting for the entire [HANGERS] column that looks for the name entered in every cell and compares it to the master list. If it does not find the exact match name in the master list it turns the cell RED.

Thank you!

· Share on FacebookShare on Twitter

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭

    Hi @bkg73123

    You will need to create a helper column containing a formula to call out the values that don't appear and use that in your conditional format. The helper would potentially have the formula =IF(HAS([Building]623:[Building]648, [Column which has the names you're trying to match against the list]@row), "Yes", "No"). This will make a Yes when the name is found and No when it is not. You can then use this helper column with values No to turn the Hangers column to Red in Conditional Formatting settings.

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

    · Share on FacebookShare on Twitter
  • bkg73123
    bkg73123 ✭
    https://community.smartsheet.com/discussion/comment/450746#Comment_450746

    Is there any special considerations for blanks. I would want blanks to effectively be ignored in the column with names to be validated and the range of master names that is defined. I would like the master list range to have at least 10 blanks so names can be added later without having to modify formulas. Thank you!

    · Share on FacebookShare on Twitter
  • AravindGP
    AravindGP ✭✭✭✭✭✭

    Hi @bkg73123

    You can exclude blanks from your search by doing a nested IF statement.

    =IF(Column which has the names you're trying to match against the list]@row) = "", "", IF(HAS([Building]623:[Building]648, [Column which has the names you're trying to match against the list]@row), "Yes", "No"))

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

    · Share on FacebookShare on Twitter
  • bkg73123
    bkg73123 ✭

    · Share on FacebookShare on Twitter
  • bkg73123
    bkg73123 ✭

    Disregard my previous post I see my errors.

    It works. Thank you!

    · Share on FacebookShare on Twitter
  • bkg73123
    bkg73123 ✭

    Is there a way to turn it into a column formula? A way to reference that master list of names without referencing the cells so it can be used as a column formula?

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!