URGENT: Need help with data validation and nesting if statements

[gmaboing]

Need help with nesting and data validation

BASIC BACKGROUND INFO: I have a spreadsheet that I am doing data validation with multiple dependent lists. I have setup a validation list for my first instance, and for my second instance, instead of using the 'Indirect()' command, I decided to do a 7-layered nested 'if' statement in the data validation field because I need very specific results--this is working perfectly.

PROBLEM: I am trying to do my third and final data validation box but my problem is that there are 26 possiblities or combinations that need to be built in. For each possibility or combination, the user will then be displayed a range of possible options from the data validation list--some lists will be as short as 2, and others as long as 24. I know that Excel 2003 had a limit of eight nesting commands and 2007 has 64, but I am currently running 2003. Is there a function that I can use to help build my logic in the data validation box?

Ultimately, I would build out the 26 level nesting formula (yes, manually type it out) like this:
(by the way, this is the nesting formula inside my second data validation box that works)
=IF(AA4=CC5,CC12:CC13,
IF(AA4=CD5,CD12,
IF(AA4=CE5,CE12,
IF(AA4=CF5,CF12:CF17,
IF(AA4=CG5,CG12:CG18,
IF(AA4=CH5,CH12:CH16,
IF(AA4=CI5,CI12:CI15,
CA8)))))))

Help please.
Thank you

 

[Andrew Poulsom]

Welcome to the Board.

Why don't you name your ranges after the entries in row 5 and use:

=INDIRECT(AA4)

 

[gmaboing]

Hi Andrew.
Thanks for welcoming me.
The problem is that I have too many combination to try and put together. There are 26, so even if I use the INDIRECT() function, there are still too many to put in one cell.

For example:
The first drop down box has a total of 6 choices. (works perfectly)
The 2nd drop down box needs to display a seperate list of 7 choices for each of the 6 in the first drop down box. (works perfectly)
The 3rd drop down box will have 26 choices for each of the 7 choices from the 2nd drop down. (this is where I'm stuck)

Does that make sense?

 

[Andrew Poulsom]

Sorry, I don't understand. I'm not aware of any limits on the number of ranges using INDIRECT. If AA4 contains the name of a named range you only need:

=INDIRECT(AA4)

for your data validation source.

 

[gmaboing]

Yes, i'm sure I'm not explaining something very clearly.. having a hard time putting this into words.

I don't want to display all 26 items at once.

I'm going to try another example:

All of the 1st drop down box available options (these are colors):
earthtones
feminine colors


2nd drop down box avail options:
orange
green
yellow
pink
purple
pastels

3rd drop down box avail options:
burnt orange
carrot orange
amber
orange peel
shamrock green
sea green
army green
lemon
mustard
school bus yellow
light blue
teal
peach
lime green
aqua

If the user selects (1) earthtones, then in cell 2 only orange, green, and yellow will be available--I don't want to see the other items. If the user then selects orange, then only burnt orange, carrot orange, amber, and orange peel will display--I don't want to see the other items.

--OR--

If the user selects (1) fem colors, then in cell 2 only soft pink, purple, and pastels will be available--don't want to see the other items. if the user then selects pastels, then only teal and peach will display--don't want to see the other items.

Does that help any?

I have the items aranged in the correct order so that I can if I were to use an "if" statement, i could select the range of cells that should display.

I really appreciate you taking the time to try to help me with this.

 

[Andrew Poulsom]

I copied your lists into columns A:C and created the following named ranges:

<TABLE style="WIDTH: 168pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=223 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=98 height=17>List</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 94pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=125>=Sheet1!$A$1:$A$2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>earthtones</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$B$1:$B$3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>feminine_colors</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$B$4:$B$6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>orange</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$C$1:$C$4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>green</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$C$5:$C$7</TD></TR></TBODY></TABLE>

I didn't bother with the other colours but you will need to.

In D1 I set up data validation with Source =List. In D2 the source is:

=INDIRECT(SUBSTITUTE(D1," ","_"))

and in D3 the source is:

=INDIRECT(D2)

 

[gmaboing]

Aaaaaaahhhhh... will give it a try and let you know what happens.

Thanks!

 

[gmaboing]

I copied your lists into columns A:C and created the following named ranges:

<TABLE style="WIDTH: 168pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=223 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=98 height=17>List</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 94pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=125>=Sheet1!$A$1:$A$2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>earthtones</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$B$1:$B$3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>feminine_colors</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$B$4:$B$6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>orange</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$C$1:$C$4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>green</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=Sheet1!$C$5:$C$7</TD></TR></TBODY></TABLE>

I didn't bother with the other colours but you will need to.

In D1 I set up data validation with Source =List. In D2 the source is:

=INDIRECT(SUBSTITUTE(D1," ","_"))

and in D3 the source is:

=INDIRECT(D2)

ANDREW YOU ARE THE MAN!!!! HOLEY MOLEY!! EXACTLY WHAT I NEEDED!!!!!

Thank you, thank you, thank you, thank you!!!