Checking a Box When One Cell in a Row is Different

Hello Smartsheet Community - Happy Friday!

I've tried a few different formulas for this one, and have not been able to get this to work. Any assistance would be greatly appreciated.

We have a "look up" sheet that is pulling in data from (12) other sheets - one column per month. We will be using this sheet to check for changes in account holder names, by entering their ID # and then seeing the results of the data pulled in from the (12) source sheets.

We want a box to be checked if the account holder name for the ID # entered has changed over the course of the year.

Below is how the data/columns are on the lookup sheet:

Column 1= ID # (example: 12345)

Column 2 - check box to indicate the account holder name has changed/ is different in any of the next (12) columns

Columns 3-14 = (12) monthly columns containing account holder name (Jan 2023 Account Name, Feb 2023 Account Name, etc)

Thanks in advance for the assistance!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I feel like you could make this actually relatively simple. Count the number of unique names across the 12 columns. If that number is greater than 1 then there was at least one change and the box gets checked.

    =IF(COUNT(DISTINCT([Column1]@row:[Column12]@row)) > 1, 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Just so I'm clear, you want this formula to run on the lookup sheet and it should reference the next 12 columns to see if there is a change? If so then I think you would be looking at a long AND statement nested in an IF statement. Something like:

    =IF(AND(Column3@row = Column4@row, Column4@row = Column5@row, ... etc), true, false)

    Essentially this formula would narratively say, "If all of the names in the 12 columns match, then "True" or check the box, otherwise false or uncheck the box.

  • Nancy Heater
    Nancy Heater ✭✭✭✭✭

    Hi @David Tutwiler !

    Thanks for the help! That works, however we reversed the true, false to be false, true, so the box is being checked when things don't match.

    One question that we didn't think of before ... if we wanted to populate this formula out for all (12) months, but we're only in Feb now, how can we have it ignore the blanks for the months we don't have data for yet?

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    That will make the formula a bit more complicated, but still doable. I think you would need to place an OR statement for each column evaluation. Like this:

    =IF(AND(OR(Column3@row = Column4@row, ISBLANK(Column3@row), ISBLANK(Column4@row)), OR(Column4@row = Column5@row, ISBLANK(Column4@row), ISBLANK(Column5@row)), ... etc), false, true)

    This way if either the names match OR if the first column contains a Blank OR if the second column contains a blank, it still passes. Only if two names don't match will it return a name change.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I feel like you could make this actually relatively simple. Count the number of unique names across the 12 columns. If that number is greater than 1 then there was at least one change and the box gets checked.

    =IF(COUNT(DISTINCT([Column1]@row:[Column12]@row)) > 1, 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Nancy Heater
    Nancy Heater ✭✭✭✭✭

    @Paul Newcome - that worked perfectly.

    Thank you both for your input!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!