Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Summary Sheet Formulas Corrupting when Copied

    ✭✭✭✭✭
    edited 12/14/23 in Formulas and Functions

    Hello,

    I am replicating (File Save As) sheets - and I have about 300 to do, with Summary Sheet fields that contain formulas.

    The original formula counts the number of items in a specific column:

    =COUNTIFS([Compliance Status]1:[Compliance Status]22, "Compliant")

    But when it is copied to another sheet (that is identical in structure) the field shows #BLOCKED. When selecting the summary field it shows the formula as:

    =COUNTIFS([Compliance Status]1:#REF, "Compliant")

    Why is it not bringing over the complete formula?

    (I thought I posted this yesterday but it appears as though it didn't go through)

    Thanks in advance!

    Pauline

    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭✭✭
      Answer ✓

      Summary Sheet Formulas Corrupting when Copied

      Pauline J

      I believe the the #REF is due to the fact that you don't have a row 22. I recommend you update your formula so it can grow with your data. Try the following:

      =COUNTIFS([Compliance Status]:[Compliance Status], "Compliant")

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭
      Answer ✓

      Summary Sheet Formulas Corrupting when Copied

      Pauline J

      I believe the the #REF is due to the fact that you don't have a row 22. I recommend you update your formula so it can grow with your data. Try the following:

      =COUNTIFS([Compliance Status]:[Compliance Status], "Compliant")

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      Oh. My. Goodness. So simple -- you are now my new BFF! Thank you, it works!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions