If function.. Error - the specified formula cannot be entered because it used more levels of nesting....

[Sandeep Singh]

Hi All,

Hope Everyone is Doing Great !!!

Data on which I am applying formula.

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]CA[/TD]
[TD="class: xl64, width: 64"]1-99[/TD]
[TD="class: xl63, width: 64"]DH[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]100-199[/TD]
[TD="class: xl63"]NH[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]200-299[/TD]
[TD="class: xl63"]ME[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]500-599[/TD]
[TD="class: xl63"]IJ[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]600-699[/TD]
[TD="class: xl63"]UY[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]800-899[/TD]
[TD="class: xl63"]PL[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]900-999[/TD]
[TD="class: xl63"]UJ[/TD]
[/TR]
[TR]
[TD="class: xl63"]AZ[/TD]
[TD="class: xl64"]1-64[/TD]
[TD="class: xl63"]MN[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]64-389[/TD]
[TD="class: xl63"]OP[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]390-550[/TD]
[TD="class: xl63"]LA[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]551-799[/TD]
[TD="class: xl63"]SF[/TD]
[/TR]
[TR]
[TD="class: xl63"]NH[/TD]
[TD="class: xl63"]54-95[/TD]
[TD="class: xl63"]LO[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]96-155[/TD]
[TD="class: xl63"]ER[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]156-680[/TD]
[TD="class: xl63"]CO[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]681-999[/TD]
[TD="class: xl63"]GH[/TD]
[/TR]
</tbody>[/TABLE]



My If formula is ..

IF(D3="CA",IF(AND(E3>=1,E3<=99),C3,IF(AND(E3>=100,E3<=199),C4,IF(AND(E3>=200,E3<=299),C5,IF(AND(E3>=500,E3<=599),
C6,IF(AND(E3>=600,E3<=699),C7,IF(AND(E3>=800<=899),C8,IF(AND(E3>=900,E3<=999),C9,"Not InRange"))))))),IF(D3="AZ",IF(AND(E3>=1,E3<=64),C10,IF(AND(E3>=64,E3<=389),C11,IF(AND(E3>=390<=550),C12,IF(AND(E3>=551,E3<=799),C13,"Not In Range"))))))

I have many more if conditions to add on... but i am getting error "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format".

Then I have Google it to see some other formula where i can i reduce if's.

I tried this "IF(D3="CA",LOOKUP(E3,{"1-99","100-199","200-299"},{"DH","NH","ME"})) its not working.

I need some help on this... Thanks for the help in advance.

 

[MandeepBaluja]

Gud one steve

 

[MUHAMMAD IBRAHIM]

Dear FormR,


i have another question from you, let me explain you,



"sheet1!" "sheet2!"
col(A) col(B) col(C) col(A) col(B) col(C)
"roll.no" "quantity" "remaining quantity" "date" "roll no." "quantity of cutting"
1 120 85 12/4/2014 1 20
2 120 - 12/4/2014 1 15
3 1000 800 14/5/2014 3 150
6 150 - 14/6/2014 7d 50
7d 100 50 15/6/2014 3 50






above there are two sheets sheet1! and sheet2!
in sheet1! i have my main stocks data with different roll no col(A) and each have its quantity col(B) . in the


remaining col(C) it tell me that how much quantity is left . how?
in sheet2! i have 3 col .
it also has roll no. col(b) like in sheet1! and it has quantity of cutting col(C) when i enter data in col(c) of


sheet2! so it deducts the quantity from col(b) of sheet1! and tells me the remaining quantity of sheet1!


what i want to do is that when ever i enter a roll no in sheet2! with its quantity of cutting in sheet2! it


automatically tell me the right "quantity remaining with correct roll no. in sheet1!. plz help me to make a


formula.

 

[FormR]

i have another question from you, let me explain

Something like this maybe (btw - you would be better off starting your own thread for new questions)

Excel 2012
	A	B	C
1	roll.no	quantity	remaining quantity
2	1	120	85
3	2	120	120
4	3	1000	800
5	6	150	150
6	7d	100	50
Sheet1

Cell Formulas
Range	Formula
C2	=B2-SUMIF(Sheet2!B:B,A2,Sheet2!C:C)

 

[MUHAMMAD IBRAHIM]

thankyou once again, your guide is really helping me.

 

[MUHAMMAD IBRAHIM]

can you tell me how can i create template like this which you have created for me to understand .. i dont know

 

[FormR]

can you tell me how can i create template like this which you have created for me to understand .. i dont know

Take a look at post#2 here:
http://www.mrexcel.com/forum/about-board/508133-attachments.html

I use MrExcel HTML Maker

 

[MUHAMMAD IBRAHIM]

Dear FormR, can you help me with the following problem. Let me explain you,

Sheet1!

[TABLE="width: 633"]
<tbody>[TR]
[TD]ORDER NO. /NAME
[/TD]
[TD]DATE
[/TD]
[TD]ROLL.NO
[/TD]
[TD]QUANTITY OF CUTTING (YARDS)
[/TD]
[TD] SOLD RATE
[/TD]
[/TR]
[TR]
[TD]R#115
[/TD]
[TD]2/7/2014
[/TD]
[TD]129
[/TD]
[TD]4.50
[/TD]
[TD]525
[/TD]
[/TR]
[TR]
[TD]R#117
[/TD]
[TD]2/7/2014
[/TD]
[TD]179
[/TD]
[TD]5.00
[/TD]
[TD]410
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]3/7/2014
[/TD]
[TD]267
[/TD]
[TD]25.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]5/7/2014
[/TD]
[TD]267
[/TD]
[TD]2.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]93
[/TD]
[TD]1.25
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]226
[/TD]
[TD]1.25
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]R#568
[/TD]
[TD]8/7/2014
[/TD]
[TD]227
[/TD]
[TD]3.00
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]R#120
[/TD]
[TD]8/7/2014
[/TD]
[TD]129
[/TD]
[TD]1.00
[/TD]
[TD]525
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]104
[/TD]
[TD]3.50
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]159
[/TD]
[TD]7.00
[/TD]
[TD]450
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2!



[TABLE="width: 633, align: left"]
<tbody>[TR]
[TD]ENTER ORDER NO. /NAME
[/TD]
[TD]DATE
[/TD]
[TD]ROLL.NO
[/TD]
[TD]QUANTITY OF CUTTING (YARDS)
[/TD]
[TD] SOLD RATE
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]93
[/TD]
[TD]1.25
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]226
[/TD]
[TD]1.25
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]104
[/TD]
[TD]3.50
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]159
[/TD]
[TD]7.00
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]3/7/2014
[/TD]
[TD]267
[/TD]
[TD]25.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]5/7/2014
[/TD]
[TD]267
[/TD]
[TD]2.00
[/TD]
[TD]285
[/TD]
[/TR]
</tbody>[/TABLE]

















In sheet1! I have five columns . sheet1! Is my main sheet in which I enter my data . what I want to do is

In sheet2! Column name ”ENTER ORDER NO. /NAME” (“Which I have created as an example to show you what I want”) when I enter my enter my data it shows me the exact “DATE”, “ROLL.NO”, “QUANTITY OF CUTTING (YARDS)” ,” SOLD RATE”. Even if I have multiple SAME data in “ENTER ORDER NO. /NAME” . which I have shown you in sheet2!

 

[FormR]

.......

You should start a new thread for your new question.

 

[MUHAMMAD IBRAHIM]

You should start a new thread for your new question.

how should i start a new threat ???]

 

[FormR]

how should i start a new threat ???]

Click on the "Post a new thread" button at the top of this page:

Excel Questions