Need help in data validation

[sujyo]

Hello excel gurus...

I want to get Stop Alert(can not enter data) in column F3, G3, H3 when entered data(value) is more then E3 using data validation...

Example:

If E3=100 then in F3 I can enter less then and equal to 100(value of E3),same thing in column G3, H3.

The total of F3, G3, H3 can not be more then E3.

If I enter 100 in F3 then I can not enter anything in column G3 or H3.

If I enter 98 in F3 then I can enter 2 in G3 or H3. or 1 in each.

If E3=0 or empty then I can not enter anything in F3,G3,H3.

No decimals just whole value.


I tryed to make custom formula, less or more then, equal to etc but no luck. I can handle how to set up Alert Messege part. I hope you understand...Thanks in advance

 

[nazmul_muneer]

Dear Sujyo,

1. Go to F3 > Data Validation > Whole Number > Less than or equal to > type maximum =E3 > OK

2. Go to F3 > Data Validation > Whole Number > Less than or equal to > type maximum =E3-F3 > OK

2. Go to F3 > Data Validation > Whole Number > Less than or equal to > type maximum =E3-F3-G3 > OK

Done!


Regards,


Muneer

 

[Debraj]

Hi Sujyo!


Select Cell E3 to H3 > Go to Data Validation > Custom > Enter Formula as

=SUM($F3:$H3)<=$E3

It will solve your problem..

Regards,

Deb.


PS: But what about if after VALID input in F3 to H3, someone will change the data in E3..

That's I have added E3 Also in the Validation..

 

[sdsurzh]

Hi Sujyo,


Adding to the Excel Hero Debraj formula:


Select Cell F3toH3 > Go to Data Validation > Custom > Enter Formula as

=IF($E$3=0,0,SUM($F3:$H3)<=$E3)


If E3 =0 then you are not allowed to enter anything in F3toH3


Thanks,

Suresh Kumar S

 

[nazmul_muneer]

Oh Deb!

Very cute formula.


Thanks,


Muneer

 

[sujyo]

PLEASE ACCEPT MY THANKS TO YOU ALL USE THIS FORMULA...

=sum($nazmul_muneer,$Debraj Roy,$Suresh Kumar,$all),"(THANK YOU VERY MUCH"))


Thank you guys...