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.

CA	1-99	DH
	100-199	NH
	200-299	ME
	500-599	IJ
	600-699	UY
	800-899	PL
	900-999	UJ
AZ	1-64	MN
	64-389	OP
	390-550	LA
	551-799	SF
NH	54-95	LO
	96-155	ER
	156-680	CO
	681-999	GH

<tbody>
</tbody>

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!

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

<tbody>
</tbody>

Sheet2!

ENTER ORDER NO. /NAME	DATE	ROLL.NO	QUANTITY OF CUTTING (YARDS)	SOLD RATE
R#114	5/7/2014	93	1.25	180
R#114	5/7/2014	226	1.25	200
R#118	8/7/2014	104	3.50	450
R#118	8/7/2014	159	7.00	450
R#579	3/7/2014	267	25.00	285
R#579	5/7/2014	267	2.00	285

<tbody>
</tbody>

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