data validation and certain condition

N

norika

When using data validation, I encountered the following problem. Th
sitution is

Define data range as list_1, it included A, B, C, D, E, for examle.

In cell A5, I can select the desired option, for example A (one of th
list_1 data).

In cell A10, I input figure here and let B10 (previously set it havin
data validation function) is the same value of A5 automatically, but
also want to select data from list_1 if necessary, for exampl
selecting B.

I tried data/validation/custom and input "=if(A10>0,A5,list_1)". th
results showed A only but cannot choose another from list_1.

Any ideas.

Thanks in advance.

norik
 
F

Frank Kabel

Hi
AFAIK this can't be done without using some VBA code (an
event procedure). If I uderstood your example correctly
try the following:
1. Set-Up data-validation for cell B10 with just a simple
reference to your list data source.

2. Put the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A10"), Target) Is Nothing Then Exit Sub
On Error GoTo errhandler
With Target
If .Value <> "" Then
If .Offset(0, 1).Value = "" Then
Application.EnableEvents = False
.Offset(0, 1).Value = Me.Range("A5").Value
End If
End If
End With

errhandler:
Application.EnableEvents = True
End Sub
 
D

Debra Dalgleish

Instead of Custom, choose to Allow: List
and use your formula: =IF(A10>0,A5,LIST_1)

Also, you could add Custom validation to cell A10:
=IF(B10<>A5,0,ISNUMBER(A10))
so it can't be changed incorrectly after B10 is entered.
 
Top