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

Answers

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



    Hope this helps!

    Heath Hilton

  • alwayslearning
    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.

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

    Hope this helps!

    Heath Hilton

  • alwayslearning
    alwayslearning ✭✭✭✭

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

  • Heath Hilton
    Heath Hilton ✭✭
    Answer ✓

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

    Hope this helps!

    Heath Hilton

  • Andrée Starå
    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, [email protected])

    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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • alwayslearning
    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.

    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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • alwayslearning
    alwayslearning ✭✭✭✭

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @alwayslearning

    Excellent!

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • LarryHa
    LarryHa ✭✭

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

  • Ami Veltrie
    Ami Veltrie ✭✭✭✭✭✭
    edited 10/12/23

    @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
    Ami Veltrie ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!