Error on copying a formula from one row to the next row

I am using the Survey Intake - Employee Pulse Check template package to create an NPS survey sheet, report and dashboard. I've updated my questions on the template sheet and I am now editing the "helper columns" to reflect the changed questions. I created a formula to count my responses on the first row, but when I try to copy the same formula on the other rows I get an error "#INVALID OPERATION" I even tried to use the "Convert to Column Formula" but I get the same error message. Has anybody experienced something like this? How can I troubleshoot this error? - Thank you!


Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 04/30/21 Answer ✓

    Hi Silvana,

    Try this version:

    =IF([2. Please rate the PMO’s quality of service]@row ="1. Poor", 1, IF([2. Please rate the PMO’s quality of service]@row ="2. Fair", 2, IF([2. Please rate the PMO’s quality of service]@row = "3. Good", 3, IF([2. Please rate the PMO’s quality of service]@row = "4. Very Good", 4, IF([2. Please rate the PMO’s quality of service]@row ="5. Excellent", 5)))))

    Given the way you've built the responses you could try:

    =IF(ISTEXT([2. Please rate the PMO’s quality of service]@row), left([2. Please rate the PMO’s quality of service]@row,1),"")

    Either work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Silvana Luna,

    Remove the "=" before your IF statements after the 1st =IF.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • It did not help. I removed the = after the first IF and didn't work, then I removed it from all the IF (except the first one) and it didn't work either. Any other ideas?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hello,

    Please paste your formula into a response and I'll take a deeper look. Well figure this out.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thank you Mark! Appreciate the interest and dedication. Here you go:

    =IF([2. Please rate the PMO’s quality of service]@row "1. Poor", 1, IF([2. Please rate the PMO’s quality of service]@row "2. Fair", 2, IF([2. Please rate the PMO’s quality of service]@row "3. Good", 3, IF([2. Please rate the PMO’s quality of service]@row "4. Very Good", 4, IF([2. Please rate the PMO’s quality of service]@row "5. Excellent", 5)))))

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 04/30/21 Answer ✓

    Hi Silvana,

    Try this version:

    =IF([2. Please rate the PMO’s quality of service]@row ="1. Poor", 1, IF([2. Please rate the PMO’s quality of service]@row ="2. Fair", 2, IF([2. Please rate the PMO’s quality of service]@row = "3. Good", 3, IF([2. Please rate the PMO’s quality of service]@row = "4. Very Good", 4, IF([2. Please rate the PMO’s quality of service]@row ="5. Excellent", 5)))))

    Given the way you've built the responses you could try:

    =IF(ISTEXT([2. Please rate the PMO’s quality of service]@row), left([2. Please rate the PMO’s quality of service]@row,1),"")

    Either work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark you are a genius! it worked perfectly. You just made my week, thank you so much!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!