nesting help

  • Thread starter Thread starter erables40
  • Start date Start date
E

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
 
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'?
 
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'?
Click to expand...

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.
 
Back
Top