Formula changing to #REF

[DeCaelo]

The below formula links to an empty cell which is only populated once a macro is run.

=SUMPRODUCT(--('Failures-Data'!$C$2:$C$1000=$C$2),--('Failures-Data'!$B$2:$B$1000=$N$5))

When the macro runs the formula changes to:

=SUMPRODUCT(--('Failures-Data'!#REF!=$C$2),--('Failures-Data'!#REF!=$N$5))

How do I go about stopping the formula from changing?

 

[T. Valko]

The below formula links to an empty cell which is only populated once a macro is run.

=SUMPRODUCT(--('Failures-Data'!$C$2:$C$1000=$C$2),--('Failures-Data'!$B$2:$B$1000=$N$5))

When the macro runs the formula changes to:

=SUMPRODUCT(--('Failures-Data'!#REF!=$C$2),--('Failures-Data'!#REF!=$N$5))

How do I go about stopping the formula from changing?

Maybe this...

=SUMPRODUCT(--(INDIRECT("'Failures-Data'!C2:C1000")=$C$2),--(INDIRECT("'Failures-Data'!B2:B1000")=$N$5))

Note that that formula will ALWAYS refer to those ranges. If you were to insert rows/columns on that sheet the formula references will not update to reflect such.

 

[Peter_SSs]

When the macro runs the formula changes to:

=SUMPRODUCT(--('Failures-Data'!#REF!=$C$2),--('Failures-Data'!#REF!=$N$5))

How do I go about stopping the formula from changing?

Do you think it may be feasible to instead change what the macro is doing so that the formula problem doesn't occur?

 

[DeCaelo]

Maybe this...

=SUMPRODUCT(--(INDIRECT("'Failures-Data'!C2:C1000")=$C$2),--(INDIRECT("'Failures-Data'!B2:B1000")=$N$5))

Note that that formula will ALWAYS refer to those ranges. If you were to insert rows/columns on that sheet the formula references will not update to reflect such.

This works perfectly, thankyou :D

 

[T. Valko]

This works perfectly, thankyou :D

You're welcome. Thanks for the feedback!