Data validation list only when cell is FALSE

[Legacy 380435]

Hi there,

I am trying to create a drop down list in P4 that only appears if T4 = False.

T4 is dependant on the answer from D4;
=SUMPRODUCT(--ISNUMBER(SEARCH({"Near Miss","Incident"},D4)))>0

P4 needs to display list "Injury" IF T4 shows False. I have it working in essence, but if T4 comes back as TRUE then i am left with a drop down list that's blank, where i would like this cell to be truly blank with no drop down.

In data validation of P4 i have;
=IF(T4=FALSE,Lists!$G$3:$G$19, " ")

I had named "Lists!$G$3:$G$19" to Injury but regardless i am stuck at this junction, will i need VBA to achieve this?

I look forward to any help in advance haha.

 

[James006]

Hi,

To be fully automated, indeed you will need VBA ... and more precisely an Event macro which will

1. Insert the Validation and its formula ... when needed

2. Remove the Validation and its formula ... when needed ... :wink:

 

[Legacy 380435]

Hi,

To be fully automated, indeed you will need VBA ... and more precisely an Event macro which will

1. Insert the Validation and its formula ... when needed

2. Remove the Validation and its formula ... when needed ... :wink:

Ah, not my strong point. Do you know of any such similar VBA code i could adapt? I am ok on a spreadsheet but it stops there

 

[James006]

Hello,

As an initial, you can turn on your Macro recorder ...

And go once through your process ...

You will get an initial ' VBA ' translation of your actions ...

Hope this will help

 

[Legacy 380435]

Hello,

As an initial, you can turn on your Macro recorder ...

And go once through your process ...

You will get an initial ' VBA ' translation of your actions ...

Hope this will help

Cheers, will give it a go

 

[James006]

You are welcome ...

 

[Legacy 380435]

You are welcome ...

I am stuck but i think i am close to figuring it out... Basically i am not sure how to add in data validation lists with a formula

I am looking at searching column U for any FALSE
IF FALSE then add
=IF(U4=FALSE,Lists!$G$3:$G$19, " ") To column Q
If TRUE do nothing


I currently have the below but i don't know how to make it add the above formula to column Q, but i am learning a little code at least lol
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SrchRng As Range, cel As Range
Set SrchRng = Range("U3:U3000")
For Each cel In SrchRng
If SrchRng = False Then
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=IF(U4=FALSE,Lists!$G$3:$G$19, "")"
.IgnoreBlank = False
.InCellDropdown = True
End With
End Sub

 

[Legacy 380435]

Throwing up compile error;
For Without Next.

So i assume it's because i haven't selected a target cell

 

[James006]

Hello,

If you referring to a Validation List located on another sheet ... you should create a Named Range ...defined with Lists!$G$3:$G$19

Hope this will help

 

[JonXL]

Throwing up compile error;
For Without Next.

So i assume it's because i haven't selected a target cell

In the line above End Sub put

Next cel