Help improving my formula

[lomerick]

I'm getting a !VALUE error here, can someone please help me fix my formula or simplify it. Any help would be greatly appreciated.

=IF(K4&S4<>"342Prestige","Not needed"),IF(Y4="","Input Imp SAP Box",IF(OR(Y4="N6P 420",Y4="L6P 430",AB4="ICBP",K4=805),"Not needed",IF(AND(Y4="ANP 430",S4="Prestige",F4>0),"ok",IF(AND(Y4="A6P 430",S4="Prestige",F4>0),"ok",IF(AND(X4="India",F4>0),"ok",IF(OR(Y4="A6P 430",Y4="ANP 430"),IFERROR(VLOOKUP(B4&X4,GOS!A:D,4,0),"Request"),IF(C4="A553",IFERROR(VLOOKUP(B4&9,'NOS Minus'!A:F,6,0),"Request"),IF(C4=5577,IFERROR(VLOOKUP(B4&5,'NOS Minus'!A:F,6,0),"Request"),IF(OR(Y4="F6P 430",Y4="F5P 420"),IFERROR(VLOOKUP(B4&K4,'NOS Minus'!A:F,6,0),"Request"))))))))))

 

[StephenCrump]

The #VALUE problem occurs right at the start of your formula:

=IF(K4&S4<>"342Prestige","Not needed"),IF ...

If you take this bracket out and put it on the end, your formula may do what you want?

 

[lomerick]

Thanks StephenCrump, I did it already but it mess up the following conditions. It just stops at the first if condition.

 

[StephenCrump]

If you have made the correction, the formula should be returning "Not needed", "Input Imp SAP Box", "ok" or "Request". Is this working?

It just stops at the first if condition.

What do you mean by this? Do you mean the formula is returning "Not needed"? If so, it means that:

K4&S4<>"342Prestige" OR

(K4&S4="342Prestige") AND (Y4 <>"") AND (Y4="N6P 420" OR Y4="L6P 430" OR AB4="ICBP" OR K4=805)

You'll have to check these conditions ... we don't know what logic you want, and we can't see your workbook.

 

[lomerick]

If you have made the correction, the formula should be returning "Not needed", "Input Imp SAP Box", "ok" or "Request". Is this working?

It only Post Not Needed -- whatever the condition is


What do you mean by this? Do you mean the formula is returning "Not needed"? If so, it means that:

K4&S4<>"342Prestige" OR

(K4&S4="342Prestige") AND (Y4 <>"") AND (Y4="N6P 420" OR Y4="L6P 430" OR AB4="ICBP" OR K4=805)

You'll have to check these conditions ... we don't know what logic you want, and we can't see your workbook.

Formula works well until I add the =IF(K4&S4<>"342Prestige","Not needed"I still don't know where to put it. It messes the whole formula.

 

[lomerick]

Or how to properly put it in a Nested If condition.

 

[StephenCrump]

This formula:

=IF(K4&S4<>"342Prestige","Not needed",IF(Y4="","Input Imp SAP Box",IF(OR(Y4="N6P 420",Y4="L6P 430",AB4="ICBP",K4=805),"Not needed",IF(AND(Y4="ANP 430",S4="Prestige",F4>0),"ok",IF(AND(Y4="A6P 430",S4="Prestige",F4>0),"ok",IF(AND(X4="India",F4>0),"ok",IF(OR(Y4="A6P 430",Y4="ANP 430"),IFERROR(VLOOKUP(B4&X4,GOS!A:D,4,0),"Request"),IF(C4="A553",IFERROR(VLOOKUP(B4&9,'NOS minus'!A:F,6,0),"Request"),IF(C4=5577,IFERROR(VLOOKUP(B4&5,'NOS minus'!A:F,6,0),"Request"),IF(OR(Y4="F6P 430",Y4="F5P 420"),IFERROR(VLOOKUP(B4&K4,'NOS minus'!A:F,6,0),"Request")))))))))))

"works" for me. That is, it returns a value. In my case, K4&S4<>"342Prestige", so that value is "not needed".

Whether it works for you will depend on whether you have all the conditions set correctly to return "Request", "Not needed", "Input Imp SAP Box" or "ok" as you require. You'll need to test that based on what's in your workbook.

 

[lomerick]

576

2230

ok

No Export Cost

Not needed

89

F6P 430

Not Yet Actionable

Baby Care

Done

In-process

In-process

9428

UKRAINE

F5P 420

Not Actionable

In-process

In-process

<colgroup><col width="80"><col width="79"><col width="112"><col width="105"><col width="150"><col width="110"><col width="89"><col width="121"><col width="149"><col width="93"><col width="143"><col width="91"><col width="111"><col width="181"><col width="129"><col width="149"><col width="132"><col width="80"></colgroup><tbody>
</tbody>

Please try this sample Start cell column K4 to AB4

Without Data on the lookup tab from this formula
IF(OR(Y25="F6P 430",Y25="F5P 420"),IFERROR(VLOOKUP(B25&K25,'NOS Minus'!A:F,6,0),"Request"

It should POST "Request" but with the formula above it POST "Not Needed"

 

[lomerick]

What I want to add is IF K4=342 and S4 is not equal to "Prestige"

to this formula:

=IF(Y4="","Input Imp SAP Box",IF(OR(Y4="N6P 420",Y4="L6P 430",AB4="ICBP",K4=805),"Not needed",IF(AND(Y4="ANP 430",S4="Prestige",F4>0),"ok",IF(AND(Y4="A6P 430",S4="Prestige",F4>0),"ok",IF(AND(X4="India",F4>0),"ok",IF(OR(Y4="A6P 430",Y4="ANP 430"),IFERROR(VLOOKUP(B4&X4,GOS!A:D,4,0),"Request"),IF(C4="A553",IFERROR(VLOOKUP(B4&9,'NOS Minus'!A:F,6,0),"Request"),IF(C4=5577,IFERROR(VLOOKUP(B4&5,'NOS Minus'!A:F,6,0),"Request"),IF(OR(Y4="F6P 430",Y4="F5P 420"),IFERROR(VLOOKUP(B4&K4,'NOS Minus'!A:F,6,0),"Request")))))))))) I even tried inserting it to:

<colgroup><col width="150"></colgroup><tbody>
</tbody>

IF(OR(Y4="N6P 420",Y4="L6P 430",AB4="ICBP",K4=805, K4&S4<>"342Prestige"),"Not needed

Or am I doing it wrong?

 

[ABSAIFI]

QUESTION NOTE:
A	B
1 BOX = 12 PKT	1 BOX = 9 PKT
1 PKT = 25PCS	1 PKT = 18PCS
ITEM NAME	TOTAL QTY (PCS)	BOX/PKT
A	250	10p
B	306	1b 8p

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I WANT TO KNOW HOW I CAN FIX THE TOTALIZER FOR BOXES AND PKTS