display a drop-down list based on the content of another cell

J

Joe S

I want to display a drop-down validation list for a target cell only if the
vlaue in another cell meets or exceeds a numerical threshold. If the
threshold is not met, I want to force the target cell to remain blank.


Cell A contents -----> Cell B contents
< 100 must be blank
= 100 may only be "red" or "green" or "blue"
Click to expand...

Using MS Office Excel 2003
 
V

vezerid

Joe,
without VBA you can have a Custom data validation with the following
formula:
=IF(A1<100, B1="", OR(B1="a", B1="b", B1="c"))

This option, however, will not display a drow-down. Your other option
is to use the Worksheet_Change event macro to track changes in A1 (or
whatever the discriminant cell is).

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect(target, range("a1")) is nothing then
if range("a1").value < 100 then
with range("B1").validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop,
_
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
else
with range("b1").validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$F$1:$F$3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
end with
end if
end if
End Sub

This code was partly produced by the marco recorder. Change range("a1")
and range("b1") to whatever cells necessary.
To use this code right-click on the sheet tab, select View Code and
this will take you to the sheet module in VBA. Paste this code.

HTH
Kostis Vezerides
 
B

Biff

Hi!

One way:

Make a list of the acceptable entries and give that list a defined name:

J1 = red
J2 = green
J3 = blue

Insert>Name>Define
Name: List
Refers to: =Sheet1!$J$1:$J$3

Select the cell to apply the drop down
Goto Data>Validation
Allow: list
Source: =CHOOSE((A1>=100)*1,List)

You may get a message that says something to the effect: The source
currently evaluates to an error........Do you want to continue? Just answer
YES.

If cell A1 >=100 then the drop down selections will be red, green or blue.

If cell A1<100 the drop down arrow will appear (when the drop down cell is
selected) but no selections will be available.

Biff
 
R

RagDyer

That's a nice one Biff.
Did you just come up with it?

You didn't suggest it a couple of days ago in the thread we shared with
Stilla.

How about we build on it so that the OP's request of
"force the target cell to remain blank"
is closer to being met?
Of course, the user can always "copy & paste" into the validated cell(s),
but this should prevent a simple invalidated typed entry.


J1:J3 = Red, Green, Blue
J4 contains *nothing* (blank - empty)
"Ignore Blank" is *unchecked*

Enter this into the source box:

=CHOOSE((A1>=100)+1,J4,J1:J3)

This should pretty much do what the OP asked for.
Of course, all based on your original thinking.<g>
 
R

RagDyer

Sorry, posted the wrong formula.

Should be:

=CHOOSE((A1>=100)+1,list2,list1)

Where list2 is J4 defined,
And list1 is J1:J3 defined.
 
B

Biff

Did you just come up with it?

No, that's *my* preferred method when needing conditional or dependent drop
downs. The other more popular method seems overly complicated to me
(especially if the source ranges are dynamic). The only drawback to using
Choose is the limit of 29 arguments.
You didn't suggest it a couple of days ago in the thread we shared with
Stilla.
Click to expand...

I didn't think it applied, but now that you mention it ........
How about we build on it so that the OP's request of
"force the target cell to remain blank"
is closer to being met?
Of course, the user can always "copy & paste" into the validated cell(s),
but this should prevent a simple invalidated typed entry.


J1:J3 = Red, Green, Blue
J4 contains *nothing* (blank - empty)
"Ignore Blank" is *unchecked*

Enter this into the source box:

=CHOOSE((A1>=100)+1,J4,J1:J3)
Click to expand...

Yeah, that'll work, but how does my method not fulfill:
How about we build on it so that the OP's request of
"force the target cell to remain blank"
is closer to being met?
Click to expand...

Biff
 
R

RagDyer

Yours does work so, I guess I stuck my foot in my mouth again.
Although, to be truly accurate, you did forget to mention about unchecking
"Ignore Blank", and I tested your way that way.
 
Top