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
-
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!
Answers
-
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.
-
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?
-
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.
-
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!
-
@Paul Newcome - that worked perfectly.
Thank you both for your input!!!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!