Please help with data validation issue

[unforgiven058]

I have an incomplet worksheet I am trying to complete. I have created a data validation list that allows user to plug between 3 values(none, performance award, and special commendation). Based on these values I have the next column set up to allow only certain dollar amounts to signify how much the award is worth based on what the chosen criteria was. I did this with embedded if statements. It works fine as long as you dont change the first column. for example: if i have none selected then the only dollar amount allowed is "0". If i were to change to special commendation (which allows between $500-$5000), this doesn not get rid of the previous zero amount that was there when it used to be labled "none"....nor does it give me an error which it should because zero would no longer be a valid entry for that selection. in order to get an error i would have to select the dollar section and reenter the zero...or if i wanted to complete the form, i would have to enter a number within the valid criteria...but the excel program does not seem to recalculate when you change the list. it re-calculates fine with any other formula.....anyone have any info on this?

 

[NARAYANK991]

Hi ,


This behaviour is normal ; Data Validation works only when you try to enter data in the cell which has DV.


In your example , suppose you have your selection of the three values "None" , "Performance Award" and "Commendation" in cell F5 ; F5 has to have DV , with List and these values as the list.


Now , suppose cell G5 allows you to enter the amount ; G5 also has to have DV , with Custom , and what ever formula you wish to use.


Since G5 itself does not have a formula ( it is the DV which has the formula ) , there is no way G5 can automatically change to reflect a change in F5.


The DV in G5 will do one of the following , only when you enter fresh data in it :


1. If your data entry is permitted , it will allow the entered value to remain.


2. If the entered value is not according to the DV , the entered value will not be accepted ; either you re-enter a valid value , or you press the ESC key to let the original value remain.


What you can do , is use a Worksheet_Change event macro , to change the contents of G5 when ever the contents of F5 change ; once the macro has changed G5 , you can thereafter change G5 yourself to what ever value you want ; the next time you change F5 , the macro will again change G5 to its proper default value.


Narayan