Data Validation

[maxflia10]

In range F10:F100, these cells have Data Validation. In the corresponding range H10:H100, these cells are validated on what is entered in F10:F100. In the F range using Data Validation, I have the formula

=Choose(Match(F10,NamedRange,0),Range2,Range3)

Now I need to add another parameter to range H, namely if F10 > 99, no validation.

Is this possible using code?

[Zack Barresse]

Hi!

Yes, possible using code. Not sure what you have right now in the H range for validation, or which formula you're updating, but if you have the validation formula you want, you can use something like this (assuming it's the activesheet)...
Sub ChangeDV()
Dim rDV As Range, sFormula As String
sFormula = "=YOURFORMULAHERE()"
Set rDV = ActiveSheet.Range("H10:H100")
With rDV.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:=sFormula
.InputTitle = "Input Title Here"
.InputMessage = "Input message goes here"
.ShowInput = True
.ErrorTitle = "Error Title Here"
.ErrorMessage = "Error message goes here"
.ShowError = True
End With
End Sub

Just change the formula in the second line of code there to whatever you want.

Let us know if that works Brian. :)

[maxflia10]

Aloha Zack,

Thanks!

In F10:F100, I have Data Validation to limit entries to < 99. In column H10:H100, I have setup a dependant list with the formula,

=CHOOSE(MATCH(F10,NamedRange,0),NamedRange1,NamedRange2)

If F contains 1, use range1 in H, if 2, use range2 in H etc. This works OK with Data Validation.

Now I need to add another parameter. If F10:F100 > 99, no validation in both F10:F100 and H10:H100.

[Zack Barresse]

Hmm, I'm lost. If you have current validation in F range to limit entries to < 99, then how would they be > 99? I'm assuming it must not be an absolute validation?

Could your formula logic look like...

=(F10<99)*(CHOOSE(MATCH(F10,NamedRange,0),NamedRange1,NamedRange2))

[maxflia10]

OK Zack, let's start from scratch, no validation in the sheet.

If F10 =1, allow a value from NameRange1 in H10

If F10 = 2 allow a value from NameRange2 in H10

If F10 > 99, no validation, allow anything in H10

Sorry for the confusion! :confused4

[Aussiebear]

Just so we can clear it up a bit more....

Are there only 3 values? (1,2 & greater than 99)?
How many named ranges are there( NamedRange0, NamedRange1, NamedRange2, NamedRange3)?

Otherwise I'm thinking the following type of formula might be somewhere near the mark, based on what you have indicated in post#5

=IF(F10=1,Choose(Match(F10,NamedRange,0),Range1),IF(F10=2,Choose(Match(F10, NamedRange,0),Range2),IF(F10>99,F10,"")))

[maxflia10]

Aloha Guys,

Thanks for your feedback.

There are 16 values and 16 named ranges to complete the depedant list, plus if > 99 no validation....