complicated validation

[BorisS]

I need a validation that takes either a dropdown list with an indirect(A1)
reference off of one cell in the case that a second cell has "internal" in
it, or an "any value" validation if that second cell says "external".
Example:

A B C
1 test external
2 bed internal
3 test internal

C1 should validate to any value the use wants to put in
C2 should validate to a dropdown list with the values from named range "bed"
c3 should validate to a dropdown list with values from named range "test"

Any way to accomplish this?
--
Boris

[Biff]

Hi!

This would require VBA to do EXACTLY as you want.

If nobody replies with a VBA solution you might want to try this:

Create an additonal named range, named: External
Have it refer to a range of 3 cells, say, J1:J3

Enter these values in:

J1 = Hit ESCAPE
J2 = then you may
J3 = enter any value

Select the cells to apply the validation.
Allow List
Source:

=IF(B1="External",INDIRECT(B1),INDIRECT(A1))

To make this work you must disable the Error Alert which allows the user to
enter any value.

So, when a user selects a cell that corresponds to External and clicks the
drop down arrow they will see:

Hit ESCAPE
then you may
enter any value

Hopefully, that should be self-explanatory! (but ya never know!)

When the user selects a cell that corresponds to Internal then the named
range that corressponds to the cell in column A will be the source of the
drop down. Having the Error Alert disabled allows the user to enter any
value under either condition. Not ideal, but even if the Error Alert is
enabled users can still defeat it by dragging a cell value or pasting into
the validated cell.

Just something to consider if you don't get any VBA solutions.

Biff

"BorisS" <[email protected]> wrote in message
news:[email protected]...
>I need a validation that takes either a dropdown list with an indirect(A1)
> reference off of one cell in the case that a second cell has "internal" in
> it, or an "any value" validation if that second cell says "external".
> Example:
>
> A B C
> 1 test external
> 2 bed internal
> 3 test internal
>
> C1 should validate to any value the use wants to put in
> C2 should validate to a dropdown list with the values from named range
> "bed"
> c3 should validate to a dropdown list with values from named range "test"
>
> Any way to accomplish this?
> --
> Boris

[Rowan]

Here is one VBA solution assuming you really are talking about columns A
, B and C. (I have assumed headers in row1).

Sub Vld()
Dim eRow As Long
Dim ColC As Range
Dim cell As Range
eRow = Cells(Rows.Count, 2).End(xlUp).Row
Set ColC = Range(Cells(2, 3), Cells(eRow, 3))
For Each cell In ColC
If UCase(cell.Offset(0, -1).Value) = "INTERNAL" Then
With cell.Validation
.Delete
.Add Type:=xlValidateList _
, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & cell.Offset(0, -2).Value
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
Next cell
End Sub

Hope this helps
Rowan

BorisS wrote:
> I need a validation that takes either a dropdown list with an indirect(A1)
> reference off of one cell in the case that a second cell has "internal" in
> it, or an "any value" validation if that second cell says "external".
> Example:
>
> A B C
> 1 test external
> 2 bed internal
> 3 test internal
>
> C1 should validate to any value the use wants to put in
> C2 should validate to a dropdown list with the values from named range "bed"
> c3 should validate to a dropdown list with values from named range "test"
>
> Any way to accomplish this?

[Rowan]

I should point out that this adds the validation which is then static
i.e changes to columns A and B will not affect the validation in column
C. From this point of view (probably amongst others) Biff's solution is
superior.

Regards
Rowan

Rowan wrote:
> Here is one VBA solution assuming you really are talking about columns A
> , B and C. (I have assumed headers in row1).
>
> Sub Vld()
> Dim eRow As Long
> Dim ColC As Range
> Dim cell As Range
> eRow = Cells(Rows.Count, 2).End(xlUp).Row
> Set ColC = Range(Cells(2, 3), Cells(eRow, 3))
> For Each cell In ColC
> If UCase(cell.Offset(0, -1).Value) = "INTERNAL" Then
> With cell.Validation
> .Delete
> .Add Type:=xlValidateList _
> , AlertStyle:=xlValidAlertStop, Operator:= _
> xlBetween, Formula1:="=" & cell.Offset(0, -2).Value
> .IgnoreBlank = True
> .InCellDropdown = True
> End With
> End If
> Next cell
> End Sub
>
> Hope this helps
> Rowan
>
> BorisS wrote:
>
>> I need a validation that takes either a dropdown list with an
>> indirect(A1) reference off of one cell in the case that a second cell
>> has "internal" in it, or an "any value" validation if that second cell
>> says "external". Example:
>>
>> A B C
>> 1 test external
>> 2 bed internal
>> 3 test internal
>>
>> C1 should validate to any value the use wants to put in
>> C2 should validate to a dropdown list with the values from named range
>> "bed"
>> c3 should validate to a dropdown list with values from named range "test"
>>
>> Any way to accomplish this?

[Rowan]

And finally, if you did want it to be dynamic then use the
worksheet_change event like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 And Target.Count = 1 Then
Dim tRow As Long
tRow = Target.Row
Cells(tRow, 3).Validation.Delete
If Cells(tRow, 1).Value <> Empty _
And Cells(tRow, 2).Value <> Empty Then
If UCase(Cells(tRow, 2).Value) = "INTERNAL" Then
With Cells(tRow, 3).Validation
.Add Type:=xlValidateList _
, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Cells(tRow, 1).Value
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End If
End If

End Sub

This is worksheet event code. Right click the sheet tab, select View
Code and paste the code in there.

Regards
Rowan

BorisS wrote:
> I need a validation that takes either a dropdown list with an indirect(A1)
> reference off of one cell in the case that a second cell has "internal" in
> it, or an "any value" validation if that second cell says "external".
> Example:
>
> A B C
> 1 test external
> 2 bed internal
> 3 test internal
>
> C1 should validate to any value the use wants to put in
> C2 should validate to a dropdown list with the values from named range "bed"
> c3 should validate to a dropdown list with values from named range "test"
>
> Any way to accomplish this?

[BorisS]

Rowan, thanks so much. I am only concerned in trying this without having any
earthly clue what this does. I have to admit, it's probably a good solution,
but my level of VBA ends WAY before yours, so it's hard for me to understand
what you're doing here. I know it's a pain, but if you want to provide a
lesson which would be greatly valued, "translating" your lines into English
(ie "ctrl-ups to next row", "selects entire row based on X size") would be
greatly greatly appreciated. I am always interested in this kind of stuff,
but never have asked for someone to explain some of these things to me. Like
I said, if you have a few extra minutes.
--
Boris


"Rowan" wrote:

> And finally, if you did want it to be dynamic then use the
> worksheet_change event like this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column < 3 And Target.Count = 1 Then
> Dim tRow As Long
> tRow = Target.Row
> Cells(tRow, 3).Validation.Delete
> If Cells(tRow, 1).Value <> Empty _
> And Cells(tRow, 2).Value <> Empty Then
> If UCase(Cells(tRow, 2).Value) = "INTERNAL" Then
> With Cells(tRow, 3).Validation
> .Add Type:=xlValidateList _
> , AlertStyle:=xlValidAlertStop, Operator:= _
> xlBetween, Formula1:="=" & Cells(tRow, 1).Value
> .IgnoreBlank = True
> .InCellDropdown = True
> End With
> End If
> End If
> End If
>
> End Sub
>
> This is worksheet event code. Right click the sheet tab, select View
> Code and paste the code in there.
>
> Regards
> Rowan
>
> BorisS wrote:
> > I need a validation that takes either a dropdown list with an indirect(A1)
> > reference off of one cell in the case that a second cell has "internal" in
> > it, or an "any value" validation if that second cell says "external".
> > Example:
> >
> > A B C
> > 1 test external
> > 2 bed internal
> > 3 test internal
> >
> > C1 should validate to any value the use wants to put in
> > C2 should validate to a dropdown list with the values from named range "bed"
> > c3 should validate to a dropdown list with values from named range "test"
> >
> > Any way to accomplish this?
>

[Rowan]

Hi Boris

I'll have a go at explaining this line by line.

"Private Sub Worksheet_Change(ByVal Target As Range)"
This is a worksheet change event which means it runs every time any
cell/s on the sheet is/are changed. Target is a range object variable
which is the cell or cells that have been changed.

"If Target.Column < 3 And Target.Count = 1 Then"
This line checks that the cell changed is in columns A or B ie less than
column number 3 and that only one cell has been changed ie count = 1. If
either of these conditions is not met then nothing happens and the event
ends.

"Dim tRow As Long"
"tRow = Target.Row"
A variable called tRow is defined and set to be equal to the row of the
cell that has been changed i.e target.

"Cells(tRow, 3).Validation.Delete"
The existing validation in column C of the row in which the cell has
been change (tRow) is deleted

"If Cells(tRow, 1).Value <> Empty _
And Cells(tRow, 2).Value <> Empty Then"
This line checks that there are values in both column A and B of tRow.
If not nothing more is done. So if you did have validation in C5 and
then deleted the value in A5 then validation in C5 is removed and not
re-instated until A5 is populated again.

"If UCase(Cells(tRow, 2).Value) = "INTERNAL" Then"
Checks that the value is column B of tRow is Internal. The UCASE
statement changes the value into all upper case so that this check is
not case sensitive. If this is not Internal then nothing further is
done. So if you have validation in C5 and then change the value of B5
from External to Internal the validation will be removed.

With Cells(tRow, 3).Validation
.Add Type:=xlValidateList _
, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Cells(tRow, 1).Value
.IgnoreBlank = True
.InCellDropdown = True
End With
Finally if all the requirements above are met i.e only one cell has been
changed, that cell is in either column A or B, both column A and B in
the row have values and the value in column B is Internal then standard
excel Data Validation is added to the cell in Column C. The validation
allows selection from a list and the list is a named range = to the
value in column A i.e. Formula1:="=" & Cells(tRow, 1).Value

End If
End If
End If
End Sub
And that's it.

I hope this has made it a little clearer. As with anything you are
testing I would suggest you save your work before trying this. If you
already have data on the sheet you could run the original macro I posted
to setup the validation. Then add this change event to keep the
validation dynamic so that it will respond to any changes that are made
to the sheet.

Regards
Rowan

BorisS wrote:
> Rowan, thanks so much. I am only concerned in trying this without having any
> earthly clue what this does. I have to admit, it's probably a good solution,
> but my level of VBA ends WAY before yours, so it's hard for me to understand
> what you're doing here. I know it's a pain, but if you want to provide a
> lesson which would be greatly valued, "translating" your lines into English
> (ie "ctrl-ups to next row", "selects entire row based on X size") would be
> greatly greatly appreciated. I am always interested in this kind of stuff,
> but never have asked for someone to explain some of these things to me. Like
> I said, if you have a few extra minutes.