Circular reference help!

[Guest]

Hi,

I have formulas in A1,B1,C1 etc in worksheet2 to populate the data from
worksheet1. In one of the columns in worksheet2, lets say F1, i have a
condition in such a way that if the corresponding value in WS1 is 0 then the
whole row should be empty. In other words, even tho A4, B4, C4, have formulas
and some value populated in it, if F4 has 0 the whole 4th row should be
blank...is there any way i do this? Any help wld be appreciated. Thanks

 

[Guest]

Yes, you can add an IF statement to each of your formulas (A1, B1, C1...)

So, in A1 you could enter:

=IF(F1=0,"",your formula)

This first checks F1 to see if it equals 0. If TRUE, then it returns a
blank "". If FALSE, then it performs your formula.

Repeat for B1, C1 and any others.

HTH,
Elkar

 

[Guest]

You could use this formula:

=IF(ISERROR(MATCH(0,Sheet1!$A$1:$F$1,0)),Sheet1!A1,"")

where Sheet1!$A$1:$F$1 are the boundries in worksheet one of the row you are
checking (Note, if you want to fill-down this formula, you should probably
write it: Sheet1!$A1:$F1)

and Sheet1!A1 is the corresponding cell in Sheet1 to the cell where you are
entering this formula in Sheet2 (which will have data if there is not a zero
in this row)

 

[Guest]

Thanx

Yes, you can add an IF statement to each of your formulas (A1, B1, C1...)

So, in A1 you could enter:

=IF(F1=0,"",your formula)

This first checks F1 to see if it equals 0. If TRUE, then it returns a
blank "". If FALSE, then it performs your formula.

Repeat for B1, C1 and any others.

HTH,
Elkar

 

[Guest]

Thank you

You could use this formula:

=IF(ISERROR(MATCH(0,Sheet1!$A$1:$F$1,0)),Sheet1!A1,"")

where Sheet1!$A$1:$F$1 are the boundries in worksheet one of the row you are
checking (Note, if you want to fill-down this formula, you should probably
write it: Sheet1!$A1:$F1)

and Sheet1!A1 is the corresponding cell in Sheet1 to the cell where you are
entering this formula in Sheet2 (which will have data if there is not a zero
in this row)