Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

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

Avatar of undefined
Last Comment
route217
Avatar of Rgonzo1971
Rgonzo1971

Could you send a dummy?
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Sorry....cannot from my current location....apologies..
Avatar of Rgonzo1971
Rgonzo1971

In which cell is the validation?
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

H7...
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Blurred text
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
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Professor J
Professor J

.
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks for the excellent feedback experts and prof Jim jam too
SOLUTION
Avatar of Professor J
Professor J

Blurred text
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.
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

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...
Avatar of Professor J
Professor J

route217

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

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

Avatar of Rgonzo1971
Rgonzo1971

the line 10 is for the validation list range or what is your validation
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo