Purchase Order Form

[Ice Man]

I am trying to create a PO form with some variable conditions and need
guidance.

Field $G$16=default Multiplier for many or all line items.
Starting at row 19, Col B=Qty. F=Multiplier Over-ride. G=Unit Cost. H=Unit
Price.

When getting a quote, some times the vendor provides a multiplier factor
($G$16) and the Unit Cost (G19 through G-whatever). Field $G$16 will be
either empty (Delete), blank (space bar) or have a decimal value (eg .2275).

Column F is an over-ride field of the Multiplier. It will be empty
(Delete), blank (space bar), a character that causes the Multiplier to be
ignored or a different Multiplier value (eg: .2125).

Column H is our Unit Price by considering the Unit Cost (Col G) and any
Multiplier and over-ride.

If Qty (Col B) does not contain a number value, field H in this row has no
value. It may display $ - , or display nothing even if there is a Unit
Price (Col G), a Multiplier ($G$16) or an over-ride (Col F). A text msg
alerting the user there is no Qty entered would be great but not a 'must
have'.

If a Qty (Col B) exists;
If there is no Multiplier and no Multiplier over-ride, field H will be the
value from G
If there is a Multiplier and no over-ride value, Field H is the product of G
* $G$16
If there is a Multiplier but field F="x" or "X", field H will be the value
from G
If there is or is no Multiplier and field F has a value, field H is the
product of G * F
The default multiplier $G$16 is superceded by the over-ride.

I have this working about 85% but won't contaminate your thinking with mine
so I won't include the formula I have to this point unless it is asked for.
Thank you for being here.

 

[John C]

In cell H19, type the following formula:
=IF(B19<1,"Quantity Not
entered!",IF(F19="x",G19,G19*IF(ISNUMBER(F19),F19,IF(ISNUMBER($G$16),$G$16,1))))

This is under the assumption there may be there may be characters in column
F that won't override, but otherwise, this should work.

 

[Ice Man]

This left several mishaps John resulting is a '0' in the result. The
treatment of the col B issue is neat.
Since I posted this query I found a formula that handles everything except
the Col B issue
=IF(OR(AND(SUM($G$16)=0,SUM(F19)=0),F19="X"),G19,IF(SUM(F19)>0,F19*G19,$G$16*G19))

I embeded the above into your solution, IF(SUM(B19)<1,"Quantity Not
Entered!",[my portion here]) having to add the SUM(B19) to allow for the user
to clear the Col B field with the space bar rather than the Del key. Without
the SUM() function the space character resulted in the calculated value for
H19 remaining instead of the text message posted.
Thanks for the assistance John. I was overcomplicating the formula until I
wrote the help post. After doing the post I did a truth table ignoring the
Col B issue and it all became clearer. Your proposal put the icing on the
cake.
Larry