Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to lookup row # based on content of another column's cell | Excel Worksheet Functions | |||
Hide/Delete entire rows based in the content of one cell | Excel Discussion (Misc queries) | |||
Creating a drop down list to change decimal value in another cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Put an autoshape in a cell based on another cells content | Excel Worksheet Functions |