Formula that compares one cell to another
Once again, I’m coming to this community for help with formulas.
Is it possible to create a formula that compares one cell to another cell (in the same sheet), and if they match, I’d like the word “VERIFIED” to automatically appear. And, if they don’t match, I’d like the word, “ERROR” to automatically appear.
I’d like to compare row #74 with row #80 in the column named Region Total. If this is possible, would you please write the formula for me? I seriously struggle with this.
Thank you!!! I hope everyone is well!
Best Answers
-
Can you post a screenshot? Or I would be happy to help outside of this SmartSheet chat?
Hope this helps!
Heath Hilton
-
Excellent!
Happy to help!
Easy to miss the commas and everything. We all do it!
Try something like this.
= IF(OR([Region Total]74 = "", [Region Total]80 = ""), "", IF([Region Total]74 = [Region Total]80, "VERIFIED", "ERROR"))
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
This one should work well. The formula would live in the "Verified?" column.
=IF([Test Cell 1]@row = [Test Cell 2]@row, "Verified", "Nope")
Hope this helps!
Heath Hilton
-
Heath - thanks for your response. Unfortunately, this didn't work. All the cells are in the same column called Region Total - including the cells I want to place this formula. I'm trying to compare row #74 with row #80 in this same column.
-
Okay, gotcha. Try this version. Here is what mine is using.
=IF([Test Column 1]1 = [Test Column 1]2, "Verified", "Error")
Yours would maybe be ...
=IF([Region Total]74 = [Region Total]80, "Verified", "Error")
Hope this helps!
Heath Hilton
-
I'm sorry. I'm still getting the message "UNPARSEABLE."
-
Can you post a screenshot? Or I would be happy to help outside of this SmartSheet chat?
Hope this helps!
Heath Hilton
-
I hope you're well and safe!
Strange! Heath's formula should work.
I agree with Heath that it would be great to see a screenshot.
- What kind of data do you have in the column?
- Is it linked from somewhere else?
- Are rows 74 and 80 active (have there been any changes to both rows)?
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
I hope that helps!
Have a fantastic week & Happy New Year!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Heath and Andree,
I can not tell you how much I struggle with this - My eyes get lost in all the commas, quotations, parentheses, etc. I looked at it with fresh eyes this morning. Somehow, I had an extra comma in Heath's formula. I removed it, and it is working! Could I impose on you to add to this formula - if it's even possible? Right now the sheet is blank - except the word "Verified" is in the cell where the formula sits.
Is there any way to add to this formula so that the cell remains blank until numbers are added to the two rows the formula is comparing? If not, no problem. I just love that this will work! Thank you again!
Nancy
-
Excellent!
Happy to help!
Easy to miss the commas and everything. We all do it!
Try something like this.
= IF(OR([Region Total]74 = "", [Region Total]80 = ""), "", IF([Region Total]74 = [Region Total]80, "VERIFIED", "ERROR"))
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
This worked perfectly! Thank you again! Happy New Year!!!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Instead of printing text can a value of cell be copied into the cell with the IF statement
-
Hello Andree! This formula works great. Here's my customized version:
=IF(OR([Utility CR]@row = "", [Telecom CR]@row = ""), "", IF([Utility CR]@row = [Telecom CR]@row, "Holistic", "Reviewing"))
Is it possible to add a third variable "Waste CR" (same Row, different column) into it and some additional logic, as described below?
Scenarios (only one will apply to each Row):
- All three columns are populated and all three are different values = Reviewing
- Two of the three columns are populated and both are different values = Reviewing
- All three columns are populated, two of the three columns are the same value = Partial (2 of 3)
- All three columns are populated and all three are the same value = Holistic
- Two of the three columns are populated and both are the same value = Holistic
- Only Utility CR is populated = Utility Only
- Only Telecom CR is populated = Telecom Only
- Only Waste CR is populated = Waste Only
I am trying to automate the CR Support Type value, to result in one of these drop down options:
- Reviewing
- Partial (2 of 3)
- Holistic
- Utility Only
- Telecom Only
- Waste Only
-
@Andrée Starå Hello Andree! I'll start a new discussion.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!