Formula that compares one cell to another

alwayslearning

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!

Heath Hilton

Can you post a screenshot? Or I would be happy to help outside of this SmartSheet chat?

Andrée Starå

@alwayslearning

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!

Heath Hilton

alwayslearning,

This one should work well. The formula would live in the "Verified?" column.

=IF([Test Cell 1]@row = [Test Cell 2]@row, "Verified", "Nope")

alwayslearning

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.

Heath Hilton

alwayslearning,

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")

alwayslearning

I'm sorry. I'm still getting the message "UNPARSEABLE."

Heath Hilton

Can you post a screenshot? Or I would be happy to help outside of this SmartSheet chat?

Andrée Starå

Hi @alwayslearning

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!

alwayslearning

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.

Screen Shot 2022-01-03 at 7.14.25 AM.png

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

Andrée Starå

@alwayslearning

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!

alwayslearning

This worked perfectly! Thank you again! Happy New Year!!!

Andrée Starå

@alwayslearning

Excellent!

You're more than welcome!

LarryHa

Instead of printing text can a value of cell be copied into the cell with the IF statement

Ami Veltrie

@Andrée Starå

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):

1. All three columns are populated and all three are different values = Reviewing
2. Two of the three columns are populated and both are different values = Reviewing
3. All three columns are populated, two of the three columns are the same value = Partial (2 of 3)
4. All three columns are populated and all three are the same value = Holistic
5. Two of the three columns are populated and both are the same value = Holistic
6. Only Utility CR is populated = Utility Only
7. Only Telecom CR is populated = Telecom Only
8. 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

Ami Veltrie

@Andrée Starå Hello Andree! I'll start a new discussion.