Formulas
 

This has to do with my purchased items reorder sheet where I have a column
that is populated with the required amount of purchased items needed to build
the amount of trailers from the schedule. Another column has the vendors
required minimum that we have to order so as to keep a certain price level.
Then I have the columns for my inventory levels and quantities of outstanding
purchase orders. My dilemma is in my €œRe Order€� column where I'm subtracting
my inventory and outstanding quantities from the required pcs needed in which
trying to also set it up so it takes into account the minimum required by the
vendor so it will only populate when it gets equal to or above the minimum
required quantities

Formulas
 

=IF(A2-B2-C2=D2,A2-B2-C2,"")
Where A2 is the required inventory level, B2 is the on hand, C2 is
outstanding, and D2 is the desired minimum order. Will return a blank until
the number needed to come up to inventory is greater than or equal to the
desired minimum order.

"Rspiva" wrote:

This has to do with my purchased items reorder sheet where I have a column
that is populated with the required amount of purchased items needed to build
the amount of trailers from the schedule. Another column has the vendors
required minimum that we have to order so as to keep a certain price level.
Then I have the columns for my inventory levels and quantities of outstanding
purchase orders. My dilemma is in my €œRe Order€� column where I'm subtracting
my inventory and outstanding quantities from the required pcs needed in which
trying to also set it up so it takes into account the minimum required by the
vendor so it will only populate when it gets equal to or above the minimum
required quantities

Formulas
 

Required Inventory On order Low Vendor Minimum Re order

24 6 0 30 30
When I did the formula as you showed below, my reorder cell is blank. The
formula should return the Vendor Minimum of 30 since I have 6 on hand and 0
on order so it would then force me to order the Vendor Min so as to satisfy
the requirement and leave a balance of 12 when the required has been used.

"BoniM" wrote:

=IF(A2-B2-C2=D2,A2-B2-C2,"")
Where A2 is the required inventory level, B2 is the on hand, C2 is
outstanding, and D2 is the desired minimum order. Will return a blank until
the number needed to come up to inventory is greater than or equal to the
desired minimum order.

"Rspiva" wrote:

This has to do with my purchased items reorder sheet where I have a column
that is populated with the required amount of purchased items needed to build
the amount of trailers from the schedule. Another column has the vendors
required minimum that we have to order so as to keep a certain price level.
Then I have the columns for my inventory levels and quantities of outstanding
purchase orders. My dilemma is in my €œRe Order€� column where I'm subtracting
my inventory and outstanding quantities from the required pcs needed in which
trying to also set it up so it takes into account the minimum required by the
vendor so it will only populate when it gets equal to or above the minimum
required quantities

Formulas
 

=IF(A2-B2-C2=E2,A2-B2-C2,E2)

I think this is what you meant, it will fill with vendor minimum unless
exceeded by required minus on hand and on order. I'm sorry, I misunderstood
"it takes into account the minimum required by the vendor so it will only
populate when it gets equal to or above the minimum required quantities". I
thought you meant you wanted it blank until it met the minimum instead of you
wanted the vendor minimum until then.

"Rspiva" wrote:

Required Inventory On order Low Vendor Minimum Re order

24 6 0 30 30
When I did the formula as you showed below, my reorder cell is blank. The
formula should return the Vendor Minimum of 30 since I have 6 on hand and 0
on order so it would then force me to order the Vendor Min so as to satisfy
the requirement and leave a balance of 12 when the required has been used.

"BoniM" wrote:

=IF(A2-B2-C2=D2,A2-B2-C2,"")
Where A2 is the required inventory level, B2 is the on hand, C2 is
outstanding, and D2 is the desired minimum order. Will return a blank until
the number needed to come up to inventory is greater than or equal to the
desired minimum order.

"Rspiva" wrote:

This has to do with my purchased items reorder sheet where I have a column
that is populated with the required amount of purchased items needed to build
the amount of trailers from the schedule. Another column has the vendors
required minimum that we have to order so as to keep a certain price level.
Then I have the columns for my inventory levels and quantities of outstanding
purchase orders. My dilemma is in my €œRe Order€� column where I'm subtracting
my inventory and outstanding quantities from the required pcs needed in which
trying to also set it up so it takes into account the minimum required by the
vendor so it will only populate when it gets equal to or above the minimum
required quantities

Formulas
 

That worked pretty good until I tested it by placing 12 in inventory and
placed an order for 30 in the order column and the re order column tells me
to order 30 more. You probably didnt misunderstand me, its probably how im
asking the question....sorry

"BoniM" wrote:

=IF(A2-B2-C2=E2,A2-B2-C2,E2)

I think this is what you meant, it will fill with vendor minimum unless
exceeded by required minus on hand and on order. I'm sorry, I misunderstood
"it takes into account the minimum required by the vendor so it will only
populate when it gets equal to or above the minimum required quantities". I
thought you meant you wanted it blank until it met the minimum instead of you
wanted the vendor minimum until then.

"Rspiva" wrote:

Required Inventory On order Low Vendor Minimum Re order

24 6 0 30 30
When I did the formula as you showed below, my reorder cell is blank. The
formula should return the Vendor Minimum of 30 since I have 6 on hand and 0
on order so it would then force me to order the Vendor Min so as to satisfy
the requirement and leave a balance of 12 when the required has been used.

"BoniM" wrote:

=IF(A2-B2-C2=D2,A2-B2-C2,"")
Where A2 is the required inventory level, B2 is the on hand, C2 is
outstanding, and D2 is the desired minimum order. Will return a blank until
the number needed to come up to inventory is greater than or equal to the
desired minimum order.

"Rspiva" wrote:

This has to do with my purchased items reorder sheet where I have a column
that is populated with the required amount of purchased items needed to build
the amount of trailers from the schedule. Another column has the vendors
required minimum that we have to order so as to keep a certain price level.
Then I have the columns for my inventory levels and quantities of outstanding
purchase orders. My dilemma is in my €œRe Order€� column where I'm subtracting
my inventory and outstanding quantities from the required pcs needed in which
trying to also set it up so it takes into account the minimum required by the
vendor so it will only populate when it gets equal to or above the minimum
required quantities

Formulas
 

=IF(A2-B2-C2<=0, "",IF(A2-B2-C2=E2,A2-B2-C2,E2))
:-) We can keep trying...
This one will leave it blank if the on hand and on order is qreater than
required, then do all the other stuff from before. Better?

"Rspiva" wrote:

That worked pretty good until I tested it by placing 12 in inventory and
placed an order for 30 in the order column and the re order column tells me
to order 30 more. You probably didnt misunderstand me, its probably how im
asking the question....sorry

"BoniM" wrote:

=IF(A2-B2-C2=E2,A2-B2-C2,E2)

I think this is what you meant, it will fill with vendor minimum unless
exceeded by required minus on hand and on order. I'm sorry, I misunderstood
"it takes into account the minimum required by the vendor so it will only
populate when it gets equal to or above the minimum required quantities". I
thought you meant you wanted it blank until it met the minimum instead of you
wanted the vendor minimum until then.

"Rspiva" wrote:

Required Inventory On order Low Vendor Minimum Re order

24 6 0 30 30
When I did the formula as you showed below, my reorder cell is blank. The
formula should return the Vendor Minimum of 30 since I have 6 on hand and 0
on order so it would then force me to order the Vendor Min so as to satisfy
the requirement and leave a balance of 12 when the required has been used.

"BoniM" wrote:

=IF(A2-B2-C2=D2,A2-B2-C2,"")
Where A2 is the required inventory level, B2 is the on hand, C2 is
outstanding, and D2 is the desired minimum order. Will return a blank until
the number needed to come up to inventory is greater than or equal to the
desired minimum order.

"Rspiva" wrote:

This has to do with my purchased items reorder sheet where I have a column
that is populated with the required amount of purchased items needed to build
the amount of trailers from the schedule. Another column has the vendors
required minimum that we have to order so as to keep a certain price level.
Then I have the columns for my inventory levels and quantities of outstanding
purchase orders. My dilemma is in my €œRe Order€� column where I'm subtracting
my inventory and outstanding quantities from the required pcs needed in which
trying to also set it up so it takes into account the minimum required by the
vendor so it will only populate when it gets equal to or above the minimum
required quantities

Formulas
 

PERFECT-That works like a charm!!!!!!
Thanks so much for bearing with me.

"BoniM" wrote:

=IF(A2-B2-C2<=0, "",IF(A2-B2-C2=E2,A2-B2-C2,E2))
:-) We can keep trying...
This one will leave it blank if the on hand and on order is qreater than
required, then do all the other stuff from before. Better?

"Rspiva" wrote:

That worked pretty good until I tested it by placing 12 in inventory and
placed an order for 30 in the order column and the re order column tells me
to order 30 more. You probably didnt misunderstand me, its probably how im
asking the question....sorry

"BoniM" wrote:

=IF(A2-B2-C2=E2,A2-B2-C2,E2)

I think this is what you meant, it will fill with vendor minimum unless
exceeded by required minus on hand and on order. I'm sorry, I misunderstood
"it takes into account the minimum required by the vendor so it will only
populate when it gets equal to or above the minimum required quantities". I
thought you meant you wanted it blank until it met the minimum instead of you
wanted the vendor minimum until then.

"Rspiva" wrote:

Required Inventory On order Low Vendor Minimum Re order

24 6 0 30 30
When I did the formula as you showed below, my reorder cell is blank. The
formula should return the Vendor Minimum of 30 since I have 6 on hand and 0
on order so it would then force me to order the Vendor Min so as to satisfy
the requirement and leave a balance of 12 when the required has been used.

"BoniM" wrote:

=IF(A2-B2-C2=D2,A2-B2-C2,"")
Where A2 is the required inventory level, B2 is the on hand, C2 is
outstanding, and D2 is the desired minimum order. Will return a blank until
the number needed to come up to inventory is greater than or equal to the
desired minimum order.

"Rspiva" wrote:

This has to do with my purchased items reorder sheet where I have a column
that is populated with the required amount of purchased items needed to build
the amount of trailers from the schedule. Another column has the vendors
required minimum that we have to order so as to keep a certain price level.
Then I have the columns for my inventory levels and quantities of outstanding
purchase orders. My dilemma is in my €œRe Order€� column where I'm subtracting
my inventory and outstanding quantities from the required pcs needed in which
trying to also set it up so it takes into account the minimum required by the
vendor so it will only populate when it gets equal to or above the minimum
required quantities