nesting help

[erables40]

I know you can't use a formula in excel 2007 which nests more than 7
deep so here is what I did

=IF(A1="cheese",$B$25:$B$32,IF(A1="Deli meats",$B$34:$B
$38,IF(A1="Desserts",$B$40:$B
$44,IF(A1="Fish",B46:B48,IF(A1="Offal",B50:B51,IF(A1="Pasta",B53,IF(A1="Red
meat",B61:B63,IF(A1="Poultry",B57:B59,B23))))))))

at the end of formula 1 I referenced cell B23, which works great, here
is formula 2 in cell B23

=IF(A1="Pizza",B55:B56,IF(A1="Sushi and sashimi",B65,IF(A1="White
meat",B67,"")))

Now here is my problem: in formula 2 ex. Pizza if it references more
than 1 cell B55:B56 I get a value error if I choose pizza. From my
list in A1 If I remove the :B56 and leave only the B55 I get no error.

Therefore if In the first formula I reference more than one cell it
works in second formula I can only reference one cell, Why is that?
And is there any way around it?

I can switch Pasta from formula 1 with Pizza with formula 2 but I have
a similar formula where I need to reference more than 7 and all
contain more than one cell reference.

BTW in cell A1 I put the formula 1 in Data validation source.

Please help
Thanks

 

[Jim Rech]

I know you can't use a formula in excel 2007 which nests more than 7 deep

Excel 2007 nests 64 levels. Not that that means you should use that many.

That's meaningless as it stands. A cell cannot hold the value of a range.
You can sum the range and you can do things with array formulas, etc., but I
don't see what your intent is here. What do you want the cell that contains
this formula to return when A1 is 'cheese'?

 

[erables40]

Excel 2007 nests 64 levels. Not that that means you should use that many.


That's meaningless as it stands. A cell cannot hold the value of a range.
You can sum the range and you can do things with array formulas, etc., but I
don't see what your intent is here. What do you want the cell that contains
this formula to return when A1 is 'cheese'?

First off sorry I am using 2003
The formula is in cell B2 Data Validation, Allow "List" and if I put
this function in source, whenever I choose lets say cheese in cell A1
the list in cell B2 shows me a list option of whatever is in B25:B32.
If A1 = Deli meats then the list in B2 shows me what is in B34:B38. It
works perfectly except that for formula2 I can't reference more than 1
cell.

 

[Gord Dibben]

Looks like dependent DV lists would serve you.

Select an item from A1 DV list and get a subset of related items in another DV
list.

Dee Debra Dalgleish's site.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP