How to make a List appear only when a condition is met?

stuckagain22

Board Regular
Joined
Aug 4, 2006
Messages
182
I was wondering if a there is a way to make a Validation List appear in a cell, only when a certain condition is met (ie: dependant on a value in a different cell.) If the condition is not met, then the cell would contain/perform a formula.

I have gone through "the famous" thread talking about conditional lists, however in that thread, the end result is that there is always a list in the thread. I don't want a list to appear if the condition is not met. Is there any way to do this?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

mbb4331

Board Regular
Joined
Dec 22, 2005
Messages
152
What about a WorksheetChange event?

If the independent cell = "List", you could run the code to add validation to the dependent cell. If the independent cell = "Formula", you could add the formula to the cell.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,822
Office Version
  1. 365
Platform
  1. Windows
stuckagain22

I have made these assumptions to provide a suggested solution:
1. The cell to contain the Validation or Formula is B1.
2. B1 is to be dependant on the contents of cell A1.
3. If A1 is blank then B1 is to be blank.
4. If A1 = "Monday" then B1 contains data Validation drawn from F1:F4
5. If A1 is anything else then B1 contains the formula =D1*2

Given the above, try this Worksheet_Change event code as a starting point:
<font face=Courier New>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Address <> "$A$1" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    Range("B1").ClearContents  <SPAN style="color:#007F00">'Not sure if you want this action</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Value = "Monday" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'Adjust text/value to suit</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$F$1:$F$4" <SPAN style="color:#007F00">'Adjust Validation list range to suit</SPAN>
            .IgnoreBlank = <SPAN style="color:#00007F">True</SPAN>
            .InCellDropdown = <SPAN style="color:#00007F">True</SPAN>
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = <SPAN style="color:#00007F">True</SPAN>
            .ShowError = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">ElseIf</SPAN> Target.Value <> "" <SPAN style="color:#00007F">Then</SPAN>
        Range("B1").Formula = "=D1*2" <SPAN style="color:#007F00">'Adjust formula to suit</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>
 

stuckagain22

Board Regular
Joined
Aug 4, 2006
Messages
182
Thanks Peter. I was hoping there would be a way to do it without code as I am not familiar with it at all. I should easily be able to throw that code into my sheet though. Thanks.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

this solution without code works for me

in the validation list-source type this kinda formula
=IF($A$1="yourstring";$B$2:$B$10;"")
if A1 = yourstring, then the source will be "$B$2:$B$10", else empty

rereading your initial post this might not be complete, but anyway I found this intresting enough to post and perhaps you can use this as a start ...

kind regards,
Erik
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,822
Office Version
  1. 365
Platform
  1. Windows
Thanks Peter. I was hoping there would be a way to do it without code as I am not familiar with it at all. I should easily be able to throw that code into my sheet though. Thanks.
If I have undestood correctly, then I don't think you can do what you want without VBA code. Post back if you need help with how to implement the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,849
Messages
5,544,645
Members
410,627
Latest member
georgealice
Top