List:Custom Data Validation

[dread]

I have the following IF statement for a custom data validation but it doesn't
work.

IF($B$1="North Sky Homes",B483:B489,IF($B$1="Rising
Star",B494:B504,IF($B$1="Universal",B509:B517,IF($B$1="StoneRidge",B522:B539,""))))


The lists in B483:B489, B494:B504, B509:B517,B522:B539 are those models
associated with the builder chosen in B1 (B1 has a data validation list where
you choose the builder)

Thanks.

[Maistrye]

I have the following IF statement for a custom data validation but it doesn't
work.

IF($B$1="North Sky Homes",B483:B489,IF($B$1="Rising
Star",B494:B504,IF($B$1="Universal",B509:B517,IF($B$1="StoneRidge",B522:B539,""))))


The lists in B483:B489, B494:B504, B509:B517,B522:B539 are those models
associated with the builder chosen in B1 (B1 has a data validation list where
you choose the builder)

Thanks.

You might try the following (I didn't include all the possibilities you have, just the first 3... the rest are just extending the if statement a bit):

=OFFSET($B$483,IF($B$1="North Sky Homes",0,IF($B$1="Rising Star", 11,26)),0,IF($B$1="North Sky Homes",7,IF($B$1="Rising Star",11,9)),1)

You can make it more dynamic than this. The first IF statement refers to number of rows that selection starts from B483. The second IF statement refers to the number of items in each section.

Scott

[dread]

OFFSET is too confusing for me. I was able to get my IF statement to work by
changing the Data Validation "Allow" criteria from custom to list.

Thank you for the suggestion.

"Maistrye" wrote:

>
> dread Wrote:
> > I have the following IF statement for a custom data validation but it
> > doesn't
> > work.
> >
> > IF($B$1="North Sky Homes",B483:B489,IF($B$1="Rising
> > Star",B494:B504,IF($B$1="Universal",B509:B517,IF($B$1="StoneRidge",B522:B539,""))))
> >
> >
> > The lists in B483:B489, B494:B504, B509:B517,B522:B539 are those
> > models
> > associated with the builder chosen in B1 (B1 has a data validation list
> > where
> > you choose the builder)
> >
> > Thanks.
>
> You might try the following (I didn't include all the possibilities you
> have, just the first 3... the rest are just extending the if statement a
> bit):
>
> =OFFSET($B$483,IF($B$1="North Sky Homes",0,IF($B$1="Rising Star",
> 11,26)),0,IF($B$1="North Sky Homes",7,IF($B$1="Rising Star",11,9)),1)
>
> You can make it more dynamic than this. The first IF statement refers
> to number of rows that selection starts from B483. The second IF
> statement refers to the number of items in each section.
>
> Scott
>
>
> --
> Maistrye
> ------------------------------------------------------------------------
> Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
> View this thread: http://www.excelforum.com/showthread...hreadid=563418
>
>