Suppressing #REF!

[OCM]

Greetings,

I used conditional formatting along with formulas in excel to color rows differently when a change in the name occurs in column C.
1. conditional formatting: format values where this formula is true
=$T2=0

2. In my helper column T, I used the following formula:
=MOD(IF(ROW()=2,0,IF(C2=C1,T1,T1+1)),2)

It works as intended. But, I would like to enhance the formula to avoid getting #REF when filling down the formula to blank fields.

=MOD(IF(ROW()=2,0,IF(C14=#REF!,#REF!,#REF!+1)),2)

My template has 50 rows, and if nothing entered in the remaining cells, I would like my helper column to show blank instead of #REF!

TIA,

Regards,


OCM

 

[Scott24x7]

OCM,
What you can do is put an IF() clause around your MOD(). So if Cell C14 is showing "#REF", that will be because some other cell doesn't have the value it's looking for yet. Let's say that cell is B14. You then do something like this (assuming B14 is blank):
=IF(ISBLANK(B14),"",MOD(IF(ROW()=2,0,IF(C2=C1,T1,T1+1)),2))

If there is some other value you can identify like 0 or TRUE, or what have you), then you can replace "ISBLANK(B14)" with that, like B14 = 0, or B14=TRUE, etc.
But use that indirect field that is influencing the field where the #REF is appearing.
You may need to rejig some of your formula in other cells to create the value you want.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[pmonett]

You can use ISERROR like this :

=IF(ISERROR(MOD(IF(ROW()=2,0,IF(C2=C1,T1,T1+1)),2)),"",MOD(IF(ROW()=2,0,IF(C2=C1,T1,T1+1)),2))

I've got nothing to hide, and I demand that you justify what right you have to ask.

 

[combo]

In the meantime, you deleted a row above, so the #REF! error.

If you like to always refer to the previous row, change the formula to:
[tt]=MOD(IF(ROW()=2,0,IF(C2=OFFSET(C2,-1,0),OFFSET(T2,-1,0),OFFSET(T2,-1,0)+1)),2)[/tt]
and copy down.

There is hard-coded reference (ROW()=2) in the formula, I would simplify the formula: put 0 in row 2 and the rest of condition in other cells.

combo

 

[OCM]

Thank you everyone for your input.
I modified my formula as below and it worked nicely.

=IF(ISERROR(MOD(IF(ROW()=2,0,IF(C2=C1,T1,T1+1)),2)),"",MOD(IF(ROW()=2,0,IF(C2=C1,T1,T1+1)),2))

Regards,

OCM