Summary Sheet Formulas Corrupting when Copied
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
Best Answer

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

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

Oh. My. Goodness. So simple  you are now my new BFF! Thank you, it works!
Help Article Resources
Categories
Check out the Formula Handbook template!