data validation based on value of another cell

**kadams99** · 10-23-2009, 06:09 PM

I can't seem to find an example of this, so thought I'd post. I'm trying to do some data validation of a cell based on the value of another cell.

If cell A1 is No, then I want cell B1 to equal 0 only.

If cell A1 is Yes, then cell B1 can be any decimal value between 0 and 50, with a default of 3.

Can this be done easily enough with a custom data validation formula?

Thanks.

**shg** · 10-23-2009, 06:16 PM

Custom validation: =IF(A1="no", B1=0, AND(MOD(B1,1)=0, B1>=0, B1<=50))

The cell value will not (does not) change when you change A1 --- that would require VBA.

**kadams99** · 10-23-2009, 06:53 PM

This worked great! Thanks.

Now, how can I make these percentages and not numbers. For instance, I'd like the user to enter 50, but it should actually represent 50% (.50). When I format the cell to be a percentage the 50 is actually 500%. Is there any way around this?

You're always so helpful - much appreciated!!

Karyn