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....
route217JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Could you send a dummy?
route217JuniorAuthor Commented:
Sorry....cannot from my current location....apologies..
Rgonzo1971Commented:
In which cell is the validation?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Learn More!
route217JuniorAuthor Commented:
H7...
Rgonzo1971Commented:
Then try in the corresponding sheet module ( change range at line 10) if c7 is empty it will delete H7
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") = ""
        Me.Range("H7").Validation.Delete
    Else
        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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
route217JuniorAuthor Commented:
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 JMicrosoft Excel ExpertCommented:
.
route217JuniorAuthor Commented:
Thanks for the excellent feedback experts and prof Jim jam too
Professor JMicrosoft Excel ExpertCommented:
route217

please put this code in that worksheet object module.

it will work as you need. it will not clear your data validation.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("G7")) Is Nothing Then
        If Target = "" Then
            Target.Offset(0, 1) = vbNullString
        End If
    End If

End Sub

Open in new window

route217JuniorAuthor Commented:
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 JMicrosoft Excel ExpertCommented:
route217

did you try my code.  it will not delete the data validation in the first place.
Rgonzo1971Commented:
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

Rgonzo1971Commented:
the line 10 is for the validation list range or what is your validation
route217JuniorAuthor Commented:
This question need a part 2....thanks for the excellent feedback and service.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.