If Formula

[Faiek]

Hi,
I have 5 cells each horizontally to each other, I would like a formula so that if one of the 5 cells is 1 the others become zero. Can anyone please advise on how to do this.

Thanks

 

[jdmc45]

Not exactly clear on the question. So assume you are using columns A to E.

For A1,

=IF(OR(B1=1,C1=1,D1=1,E1=1),0,A1)

For B1,

=IF(OR(A1=1,C1=1,D1=1,E1=1),0,B1)

For C1,

=IF(OR(A1=1,B1=1,D1=1,E1=1),0,C1)

For D1,

=IF(OR(A1=1,B1=1,C1=1,E1=1),0,D1)

For E1,

=IF(OR(A1=1,B1=1,C1=1,D1=1),0,E1)

 

[Scott Huish]

Formulas can't change the values in other cells, but VBA can.
Are the value changing by formula or direct entry into the cell?

 

[Scott Huish]

jdmc45:
All those formulas create a circular reference, and you can't have a value and a formula in the cell at the same time.

 

[jdmc45]

Ok. Is it possible to just have these formulas in seperate cells? then it would work

 

[Faiek]

Thanks for your replies. I think Hotpepper is correct these all create a circular refrence, do any of you know the appropriate vba code?

 

[jdmc45]

You could just use those formulas in the adjacent cells, such as columns F-J