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.

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

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