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