Need help with syntax

[dgardnerman]

I'm trying to add some features to an existing formula so that if G/454= "ALLTHRD" "HDD_STUDS" "THRDD_STUDS" "DEFORMBARS" or "REBAR" are used in the drop down window, the target cell M/454 will display a blank and multiply O/454 by E/454.

Here is my current syntax and link to screenshot:

=IFERROR(IF(A454<>"x","",IF(G454="BOLT","",IF(I454="","",IF(G454="PLATE",(I454*K454/144*L454)*E454,IF(G454="GRATING","",IF(G454="DECKING","",(I454/12*L454)*E454)))))),"")

https://www.dropbox.com/s/r4v9nd4byq679bh/Screen Shot 2018-10-31 at 9.10.33 AM.png?dl=0

 

[jtakw]

In your Post # 11, I Thought your understood my formula in Post # 10 (Not needing to Test Both List), since you've Changed Columns and Requirements, you can adapt it to:

Book1
	J
1
Sheet354

Cell Formulas
Range	Formula
J1	=IF(AND(A461="X",COUNTA(E461,M461,O461)=3),O461*IF(OR(G461={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"}),N461,F461),"")

 

[dgardnerman]

In your Post # 11, I Thought your understood my formula in Post # 10 (Not needing to Test Both List), since you've Changed Columns and Requirements, you can adapt it to:

	J

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet354​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J1[/TH]
[TD="align: left"]=IF(AND(A461="X",COUNTA(E461,M461,O461)=3),O461*IF(OR(G461={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"}),N461,F461),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I modified it a bit to account for some changes...
It works good, but the last thing that I want to get rid of is #VALUE in column P.
I only seem to get it when column N goes blank.

Here is the revised test worksheet to look at:

https://www.dropbox.com/s/ndfen0dwdhrislx/test sheet-per post 21.xlsx?dl=0

 

[jtakw]

Based on your Latest upload, assuming E, M, and N would either be Blank or "" or numeric, use this in P4:

=IF(AND(A4="X",COUNT(E4,M4,N4)=3),E4*N4*IF(OR(G4={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"}),M4,1),"")

 

[dgardnerman]

Yes....and No...

when I take your
=IF(AND(A4="X",COUNT(E4,M4,N4)=3),E4*N4*IF......
and use
=IF(AND(A4="X"),E4*N4*IF.......

everything works as it should, however...
If I change col G (TYPE) then col M (X WT) goes blank (namely because col G & H have to line up to return a value in the LOOKUPTABLE to col L or the result is "") I get a #Value in col P

 

[jtakw]

You keep ignoring/removing part of my formula.

My formula is:

=IF(AND(A4="X",COUNT(E4,M4,N4)=3),E4*N4*IF(OR(G4={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"}),M4,1),"")

You've removed it in Post # 24, and Post # 22, WHY??
Leave that in, and you WON'T get #VALUE

 

[dgardnerman]

try changing col G to bolt after you have selected beam col G and selecting a designation in col H....you get a blank?
I removed your ,COUNT(E4,M4,N4)=3) and I got bolt back, but I have a #VALUE

 

[jtakw]

try changing col G to bolt after you have selected beam col G and selecting a designation in col H....you get a blank?
I removed your ,COUNT(E4,M4,N4)=3) and I got bolt back, but I have a #VALUE

I have absolutely no idea what you're talking about, my formula works perfectly fine for the 2 sample files you uploaded.
The sample files have NO drop downs, I manually changed the Values, works fine for me.

 

[dgardnerman]

look at this comparison screen shot using the 2 different values.
Not sure why you are not getting a drop down window, I added that in my screen shot...

https://www.dropbox.com/s/5b1rb02k2sn9k78/comparison.png?dl=0

I think the kicker is when column L & M go blank the #VALUE comes into affect

When bolt is selected, E*N is not working.

 

[jtakw]

Since when was L a part of this equation?

Again, as I told you in Post # 25, my formula:

=IF(AND(A4="X",COUNT(E4,M4,N4)=3),E4*N4*IF(OR(G4={"Angle","Bar","Beam","Channel","Hss","Pipe","Plate"}),M4,1),"")

Must be used WITH the part highlighted in RED

This part of the formula Tests if E, M, and N does NOT contain a Number, if ONE of these cells (E, M, N) does NOT contain a Number, the formula will result in BLANK, NOT #VALUE .

 

[dgardnerman]

Just noticed why you don't see the drop down, ran into the problem showing a friend on their machine...
You must have objects set not to show in preferences. I selected "show object" on my friends computer and the drop downs came back.

Can you look at the worksheet again and figure out why bolt's don't function?