14 Nested IF statements creating different validation lists

J

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.
 
G

Greg Wilson

Perhaps this:

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

Regards,
Greg
 
D

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
 
J

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.
 
J

Jive Bunny

OK

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

Tanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top