more levels of nesting

[ruddranu]

=IF(O2="Activa",2406,IF(O2="Dio",2189,IF(O2="Aviator ID",2461,IF(O2="Aviator 2ID",2732,IF(O2="UNICORN PR",3319,IF(O2="UNICORN DZ",3466,IF(O2="Shine KDR",2469,IF(O2="Shine SDR",2687,IF(O2="Shine SDC",2850,IF(O2="Stunner SDR",2790,IF(O2="Stunner SDC",2953,IF(O2="Stunner SDC FI",3558,IF(O2="Twister KDR",2388,IF(O2="Twister SDR",2538,IF(O2="Twister SDC",2701,IF(O2="CBR ABS",7673,IF(O2="CBR STD",9007)))))))))))))))))

when run above formula error comes

The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format.

what is solution ?

 

[vds1]

Can you provide an insight on what exactly are you trying to do?

 

[RoryA]

Use a lookup table on the sheet with the text codes in the first column and the numbers in the second, then use VLOOKUP:
=VLOOKUP(O2,$A$1:$B$100,2,false)

adjust the $A$1:$B$100 to wherever you put your table.

 

[shadow12345]

I think you can only have 7 if statements in any one formula. which is why this doens't work.

What are you trying to do and perhaps it can be shortened

 

[ruddranu]

We have creat dropdown list (A Column) and in this list when we select "Activa" automatically in column B the price of Activa comes.

But there are 17 nos product in dropdown list so posted error comes

 

[AlphaFrog]

I would second what rorya had suggested. But if you really want to do it in one formula.

=HLOOKUP(O2,
{"Activa","Dio","Aviator ID","Aviator 2ID","UNICORN PR","UNICORN DZ","Shine KDR","Shine SDR","Shine SDC","Stunner SDR","Stunner SDC","Stunner SDC FI","Twister KDR","Twister SDR","Twister SDC","CBR ABS","CBR STD";
2406,2189,2461,2732,3319,3466,2469,2687,2850,2790,2953,3558,2388,2538,2701,7673,9007},2,0)

 

[vds1]

Create a table like this :

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Activa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>123</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Dio</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>154</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Aviator ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>125</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD></TR></TBODY></TABLE>

then you can use a vlookup formula and retrive the data.

 

[ruddranu]

Can you provide an insight on what exactly are you trying to do?

We have creat dropdown list (A Column) and in this list when we select "Activa" automatically in column B the price of Activa comes.

But there are 17 nos product in dropdown list so when apply if formula posted error comes

 

[ruddranu]

Can you provide an insight on what exactly are you trying to do?

I would second what rorya had suggested. But if you really want to do it in one formula.

=HLOOKUP(O2,
{"Activa","Dio","Aviator ID","Aviator 2ID","UNICORN PR","UNICORN DZ","Shine KDR","Shine SDR","Shine SDC","Stunner SDR","Stunner SDC","Stunner SDC FI","Twister KDR","Twister SDR","Twister SDC","CBR ABS","CBR STD";
2406,2189,2461,2732,3319,3466,2469,2687,2850,2790,2953,3558,2388,2538,2701,7673,9007},2,0)

thanks lot