Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Suppressing #REF!

Status
Not open for further replies.

OCM

MIS
Sep 12, 2002
220
US

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
 
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.
 
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.
 
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
 
Thank you everyone for your input.
I modified my formula as below and it worked nicely.
Code:
=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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top