14 Nested IF statements creating different validation lists

[Jive Bunny]

I am currently using a nested if statement similar to the one listed below,
although I have changed the cell references and removed all the $ symbols for
clarity, in a validation list.

=IF(A1=AA1,AB1:AZ1,IF(A1=AA2,AB2:AZ2,IF(A1=AA3,AB3:AZ3,IF(A1=AA4,AB4:AZ4,IF(A1=AA5,AB5:AZ5,IF(A1=AA6,AB6:AZ6,IF(A1=AA7,AB7:AZ7,IF(A1=AA8,AB8:AZ8,""))))))))

It works very well as depending on which of the 8 values AA1:AA8 is in the
reference cell A1 a different validation list is made available.

The problem is that I need to expand this to 14 variables instead of the
current 8.

I thought I had a solution when I decided to try putting the above equation
in one cell, a second equation covering the additional values in another cell
and tried to get the validation list to look at them both using the equation
"=IF(Equation1="",Equation2,"") but it equates to an error.

I have read through as many relevant posts as I could find on here and can’t
find a solution apart from waiting for Office12.

 

[Don Guillett]

does this help
="ab"&LOOKUP(A1,B1:B10)&":"&"az"&LOOKUP(A1,B1:B10)

 

[Biff]

Hi!

Try this:

=OFFSET(AB1,MATCH(A1,AA1:AA8,0)-1,,,25)

Biff

 

[Greg Wilson]

Perhaps this:

=OFFSET(AB1,MATCH(A1, AA1:AA14, 0)-1, 0, 1, 25)

Regards,
Greg

 

[davesexcel]

you could use a worksheet change event something like thi
Private Sub Worksheet_Change(ByVal Target As Range
If Target.Range("A1") The
If Target = 1 The
Range("A2").Selec
Selection.Value = 1
ElseIf Target = 2 The
Range("A2") = 2
ElseIf Target = 3 The
Range("A2") = 3
ElseIf Target = 4 The
Range("A2") = 4
ElseIf Target = 5 The
Range("A2") = 5
ElseIf Target = 6 The
Range("A2") = 6
ElseIf Target = 7 The
Range("A2") = 7
ElseIf Target = 8 The
Range("A2") = 8
ElseIf Target = 9 The
Range("A2") = 9
ElseIf Target = 10 The
Range("A2") = 10
ElseIf Target = 11 The
Range("A2") = 11
ElseIf Target = 12 The
Range("A2") = 12
ElseIf Target = 13 The
Range("A2") = 13
ElseIf Target = 14 The
Range("A2") = 14

End I
End I

End Su

A lookup formula would be much easier thoug

 

[Jive Bunny]

Thank you all for your help, I will try Greg and Biff's proposal first, its
new to me and might take an hour or so for me to apply it but the fact that
you both suggest the same solution is promising.

 

[Jive Bunny]

OK

That was so simple to do i'm kicking myself for not asking sooner.

Tanks again