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

#### stuckagain22

##### Board Regular
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?

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

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

Replies
11
Views
106
Replies
4
Views
55
Replies
0
Views
44
Replies
3
Views
213
Replies
6
Views
73