14 Nested IF statements creating different validation lists
 

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 cant
find a solution apart from waiting for Office12.

14 Nested IF statements creating different validation lists
 

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

--
Don Guillett
SalesAid Software

"Jive Bunny" <Jive wrote in message
...
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.

14 Nested IF statements creating different validation lists
 

Hi!

Try this:

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

Biff

"Jive Bunny" <Jive wrote in message
...
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.

14 Nested IF statements creating different validation lists
 

Perhaps this:

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

Regards,
Greg

"Jive Bunny" wrote:

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 cant
find a solution apart from waiting for Office12.

14 Nested IF statements creating different validation lists
 

you could use a worksheet change event something like this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range("A1") Then
If Target = 1 Then
Range("A2").Select
Selection.Value = 10
ElseIf Target = 2 Then
Range("A2") = 20
ElseIf Target = 3 Then
Range("A2") = 30
ElseIf Target = 4 Then
Range("A2") = 40
ElseIf Target = 5 Then
Range("A2") = 50
ElseIf Target = 6 Then
Range("A2") = 60
ElseIf Target = 7 Then
Range("A2") = 70
ElseIf Target = 8 Then
Range("A2") = 80
ElseIf Target = 9 Then
Range("A2") = 90
ElseIf Target = 10 Then
Range("A2") = 100
ElseIf Target = 11 Then
Range("A2") = 110
ElseIf Target = 12 Then
Range("A2") = 120
ElseIf Target = 13 Then
Range("A2") = 130
ElseIf Target = 14 Then
Range("A2") = 140

End If
End If

End Sub


A lookup formula would be much easier though


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=557707

14 Nested IF statements creating different validation lists
 

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" wrote:

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 cant
find a solution apart from waiting for Office12.

14 Nested IF statements creating different validation lists
 

OK

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

Tanks again

"Jive Bunny" wrote:

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 cant
find a solution apart from waiting for Office12.