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

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"

Using MS Office Excel 2003

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

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

"Joe S" <Joe [email protected]> wrote in message
news:[email protected]...
>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"
>
> Using MS Office Excel 2003

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>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Biff" <[email protected]> wrote in message
news:eQK%23qKd%[email protected]...
> 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
>
> "Joe S" <Joe [email protected]> wrote in message
> news:[email protected]...
> >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"
> >
> > Using MS Office Excel 2003
>
>

Sorry, posted the wrong formula.

Should be:

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"RagDyer" <[email protected]> wrote in message
news:eRmsBye%[email protected]...
> 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>
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
>
> "Biff" <[email protected]> wrote in message
> news:eQK%23qKd%[email protected]...
> > 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
> >
> > "Joe S" <Joe [email protected]> wrote in message
> > news:[email protected]...
> > >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"
> > >
> > > Using MS Office Excel 2003
> >
> >
>

>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.

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)

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?

Biff

"RagDyer" <[email protected]> wrote in message
news:eRmsBye%[email protected]...
> 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>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
>
> "Biff" <[email protected]> wrote in message
> news:eQK%23qKd%[email protected]...
>> 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
>>
>> "Joe S" <Joe [email protected]> wrote in message
>> news:[email protected]...
>> >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"
>> >
>> > Using MS Office Excel 2003
>>
>>
>

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Biff" <[email protected]> wrote in message
news:OaLD5Vf%[email protected]...
> >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.
>
> 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)
>
> 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?
>
> Biff
>
> "RagDyer" <[email protected]> wrote in message
> news:eRmsBye%[email protected]...
> > 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>
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> >
> > "Biff" <[email protected]> wrote in message
> > news:eQK%23qKd%[email protected]...
> >> 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
> >>
> >> "Joe S" <Joe [email protected]> wrote in message
> >> news:[email protected]...
> >> >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"
> >> >
> >> > Using MS Office Excel 2003
> >>
> >>
> >
>
>