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

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

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

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

Hi!

One way:

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

J1 = red
J2 = green
J3 = blue

InsertNameDefine
Name: List
Refers to: =Sheet1!$J$1:$J$3

Select the cell to apply the drop down
Goto DataValidation
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 wrote in message
...
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

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

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" wrote in message
...
Hi!

One way:

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

J1 = red
J2 = green
J3 = blue

InsertNameDefine
Name: List
Refers to: =Sheet1!$J$1:$J$3

Select the cell to apply the drop down
Goto DataValidation
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 wrote in message
...
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

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

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" wrote in message
...
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" wrote in message
...
Hi!

One way:

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

J1 = red
J2 = green
J3 = blue

InsertNameDefine
Name: List
Refers to: =Sheet1!$J$1:$J$3

Select the cell to apply the drop down
Goto DataValidation
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 wrote in message
...
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

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

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" wrote in message
...
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" wrote in message
...
Hi!

One way:

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

J1 = red
J2 = green
J3 = blue

InsertNameDefine
Name: List
Refers to: =Sheet1!$J$1:$J$3

Select the cell to apply the drop down
Goto DataValidation
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 wrote in message
...
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

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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Hi!

One way:

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

J1 = red
J2 = green
J3 = blue

InsertNameDefine
Name: List
Refers to: =Sheet1!$J$1:$J$3

Select the cell to apply the drop down
Goto DataValidation
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 wrote in message
...
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