asked on 12/15/2015

tricky data validation formula

Hi Experts using excel 2010

Is it possible to have a if condition with data validation...

So I have a list in data validation against a named range in the source field....=Projects...

I want to amend so that if cell G7 is blank then make H7 blank and not show current project name that was pre selected form the data validation list....

Microsoft Excel

Last Comment

route217

12/15/2015

Rgonzo1971

12/15/2015

Could you send a dummy?

route217

12/15/2015

ASKER

Sorry....cannot from my current location....apologies..

Rgonzo1971

12/15/2015

In which cell is the validation?

route217

12/15/2015

ASKER

H7...

ASKER CERTIFIED SOLUTION

Rgonzo1971

12/15/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

route217

12/15/2015

ASKER

Ok rgonzo that works fine is c7 is blank...deletes the named range....now if c7 has a value I need the data validation back in....=Projects.

Professor J

12/15/2015

route217

12/15/2015

ASKER

Thanks for the excellent feedback experts and prof Jim jam too

SOLUTION

Professor J

12/15/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

route217

12/15/2015

ASKER

Rgonzo.....I have changed line 10 to Formula1:="=Projects"....

Works fine.....however if any cell in the range c7:c1000 is blank then h7:h1000 is blank also and if we add a value in the range c7:c1000 to any cell then reapply the data validation list...

Professor J

12/15/2015

route217

did you try my code. it will not delete the data validation in the first place.

Rgonzo1971

12/15/2015

then try it will hide in white the font if c7 is empty

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("C7")) Is Nothing Then
    If Me.Range("C7") = "" Then
        Me.Range("H7").Font.Color = vbWhite
        Me.Range("H7").Validation.Delete
    Else
        Me.Range("H7").Font.Color = vbBlack
        With Me.Range("H7").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$A$1:$A$3"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End If
End If
End Sub

Open in new window

Rgonzo1971

12/15/2015

the line 10 is for the validation list range or what is your validation

route217

12/15/2015

ASKER

This question need a part 2....thanks for the excellent feedback and service.