Summary Sheet Formulas Corrupting when Copied

Pauline J

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

Eric Law

https://community.smartsheet.com/discussion/114384/summary-sheet-formulas-corrupting-when-copied

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

Eric Law

https://community.smartsheet.com/discussion/114384/summary-sheet-formulas-corrupting-when-copied

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

Pauline J

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