If value used elsewhere then conditional formatting

Stoneblue23
Stoneblue23 ✭✭
edited 12/09/19 in Smartsheet Basics

On one of my sheets we have a column with order reference numbers with the following format XYZ0123. I have been trying to set up a conditional format to warn me if I am reusing a previous number further down in the same column. So, if I enter ABC0345 and it has already been used in one of the cells further down the same column, the new cell will turn red. The conditional formatting part is simple but I cannot identify a syntax for "if value entered = value in any other cell in this column apply format". I can do this in excel but if this function available in Smartsheet? If not is there any other way to achieve the same thing?

· Share on FacebookShare on Twitter

Comments

  • Dartwohl
    Dartwohl ✭✭✭✭
    edited 01/12/18

    In one of my sheets, I have a column (column type: Flag) .  Using the pasted formula below, the flag will be checked if the cell is a duplicate.  From there, you could base conditional formatting on whether or not the the item is flagged.  Please give it a try and let me know if it works for you:

     

    =COUNTIF([YOUR COLUMN]:[YOUR COLUMN], [YOUR COLUMN]1) > 1

    · Share on FacebookShare on Twitter
  • Stoneblue23
    Stoneblue23 ✭✭

    Nice idea Dartwhohl. This works fine but I'm having a problem duplicating the formula correctly.

    My flag column is titled "Flag", Reference column (which I want to turn red if I repeat a reference) is "TC ref".Formula in flag column (for my top row in the section - 23)

    =COUNTIF([TC ref]:[TC ref], [TC ref]23) > 1

    But, when I add a new row the fomula does not repeat in the flag column. If I copy and paste the row it always refers to row 23 - not the cell relative to the formula cell

    I've also tried with relative $  - but no change

    COUNTIF([TC ref]:[TC ref], $[TC ref]$23) > 1

    Any idea what I'm doing wrong?

     

    · Share on FacebookShare on Twitter
  • Dartwohl
    Dartwohl ✭✭✭✭

    You should just be able to click the bottom right corner of the cell where you put your formula and drag it on down to the bottom of your sheet. That should adjust the formula accordingly. Make sense?

    · Share on FacebookShare on Twitter
  • Stoneblue23
    Stoneblue23 ✭✭

    Thanks Dartwhohl - makes perfect sense

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video