Keeping one of 2 cells always zero

R

routeram

Hi all of you,

I have such a requirement. There are 2 cells in which the user ca
input values. However, one of the two cells must always be zero fo
valid inputs. Is there any way to do this?

I was thinking of setting other cell to zero when user enters value i
one cell or prohibit user from entering value in a cell till the othe
is made zero but I don't seem to figure out how to do this.

Is a macro the only way out (trapping a worksheet change?)? Is i
possible to use someother functionality like data validation. I woul
also like to impose restrictions on the non-zero cell of values betwee
1e12 and 1e18.

Many thanks
 
C

Charlie

One way would be to use Data/Validation. I'm not sure
this is the best method but it works for me.

For example if cells requiring validation are D5 and E5
first enter a '0' in each cell, then highlight D5, and on
the menu bar select Data/Validation/Settings/in
the 'Allowed' box scroll to 'Custom' and in the'Formula'
box enter =IF(E5=0,1,0) click OK. Next highlight E5
following the previous steps except enter =IF(D5=0,1,0).

Should work.

Charlie O'Neill
 
D

Dave Peterson

If you don't want to use a worksheet_change event, you could use an adjacent
cell.

Format in big, bold, red letters:

=if(and(a1<>0,b1<>0),"Please fix one of these cells!","ok")
 
Top