data validation

[neveu]

hello Forum,
i have an interesting question related to data validation.

i'm trying to do the following:
when a value from another column meets a criteria the information in the celue to be filled in automatically as "ok", otherwhise to let the user choose from 2 different alternatives.

i've managed to make it work so that i have a dropdrown list that if the criteria is meet will let me know choose just "ok" from the drop down list, otherwhise will let me choose from the other 2 alternatives.

here is the formula i've inserted in the data validation field:

=IF(ISNUMBER($K2),$Y$2,$Y$3:$Y$4)

can you help me find a solution for this issue?

thank you
neveu

 

[Peter_SSs]

I think what you are asking is this. If column K is a number then automatically populate your cell with "ok" otherwise give the user a drop-down choice of the values in Y3:Y4.
Is that correct?

If so, you would need to use a macro for that. If you want to go down that path then this information would help for a start:

1. What version of Excel are you using?

2. What column is to have the ok/data validation?

3. Do the values in column K come from a formula or are they entered manually or some other way?

 

[neveu]

Hi Peter,

thank you for offering to help. my comments are in bold below your questions:



1. What version of Excel are you using?
[xl 2003]
2. What column is to have the ok/data validation?
[column M]
3. Do the values in column K come from a formula or are they entered manually or some other way?
[manual]


thank you in advance,
neveu

 

[Peter_SSs]

Please test this in a copy of your workbook.

This should get you some way closer to what you want but we may need to find out more about what you want to happen when/if values in column K are changed or deleted.

At the moment, pretty much every time you change or delete a value (or multiple values) in column K the corresponding value(s) in column M will be deleted, but I'm not sure if that is what you want. Do some testing and come back with more details if modifications are required.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Make various changes in the sheet and observe results.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> KChanged <SPAN style="color:#00007F">As</SPAN> Range, Cel <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> val<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> KChanged = Intersect(Target, _<br>                            Columns("K"), _<br>                            Rows("2:" & Rows.Count))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> KChanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cel <SPAN style="color:#00007F">In</SPAN> KChanged<br>            val = Cel.Value<br>            <SPAN style="color:#00007F">With</SPAN> Range("M" & Cel.Row)<br>                <SPAN style="color:#00007F">If</SPAN> val = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                    .Clear<br>                <SPAN style="color:#00007F">ElseIf</SPAN> IsNumeric(val) <SPAN style="color:#00007F">Then</SPAN><br>                    .Validation.Delete<br>                    .Value = "ok"<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    .Clear<br>                    <SPAN style="color:#00007F">With</SPAN> .Validation<br>                        .Delete<br>                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _<br>                            Operator:=xlBetween, Formula1:="=$Y$3:$Y$4"<br>                        .IgnoreBlank = <SPAN style="color:#00007F">True</SPAN><br>                        .InCellDropdown = <SPAN style="color:#00007F">True</SPAN><br>                        .InputTitle = ""<br>                        .ErrorTitle = ""<br>                        .InputMessage = ""<br>                        .ErrorMessage = ""<br>                        .ShowInput = <SPAN style="color:#00007F">True</SPAN><br>                        .ShowError = <SPAN style="color:#00007F">True</SPAN><br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> Cel<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>