If value used elsewhere then conditional formatting

Stoneblue23

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?

Dartwohl

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

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?

 

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?

Stoneblue23

Thanks Dartwhohl - makes perfect sense