Checking a Box When One Cell in a Row is Different

Nancy Heater

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!

Paul Newcome

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)

David Tutwiler

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

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

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

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)

Nancy Heater

@Paul Newcome - that worked perfectly.

Thank you both for your input!!!

Paul Newcome

Happy to help. 👍️