Prevent Formula Change

[Rubber Beaked Woodpecker]

In sheet1 I have the following formula which very well;

=IFERROR((COUNTIF('5.2'!$A3:C3,"0")-COUNTIF('5.2'!$A3:C3,"1")),"0")

However if I insert a new column in sheet 5.2 the formula changes to the following;

=IFERROR((COUNTIF('5.2'!$B2:D2,"0")-COUNTIF('5.2'!$B2:D2,"1")),"0")

Is there any way I can prevent the formula from changing please?

Many thanks

RBW

 

[Joe4]

One way:

=IFERROR((COUNTIF(INDIRECT("'5.2'!$A3:C3"),"0")-COUNTIF(INDIRECT("'5.2'!$A3:C3"),"1")),"0")

using INDIRECT will ensure that the range in your formula will never change as you add/remove columns/rows, as everything inside of the INDIRECT statement is treated as text.

 

[Rubber Beaked Woodpecker]

One way:

=IFERROR((COUNTIF(INDIRECT("'5.2'!$A3:C3"),"0")-COUNTIF(INDIRECT("'5.2'!$A3:C3"),"1")),"0")

using INDIRECT will ensure that the range in your formula will never change as you add/remove columns/rows, as everything inside of the INDIRECT statement is treated as text.

Brilliant thank you

 

[Joe4]

You are welcome.

Note, when marking a post as the solution, please mark the original post containing the solution, not your own post acknowledging some other post is the solution.
I have updated this for you.