Re: Inventory Formula

[cashchris2005]

Re: Inventory Formula

Hello All,

I have a inventory question that I need help with.

Lets say cell:
A1=Quantity in stock (person enters)
B1=Quantity used per day (person enters)
C1=Quantity used per week (person enters)
D1=Reorder Level (Person enters)
E1=Status (Condition formula)

What I need formulas for are
1. Subtract quantity used per day(b1) or quantity used per week (C1) from quantity in stock (a1). Then I want the total of this (a1-b1 or c1) to replace the quantity in A1 as the new quantity in stock.

After the calculation is completed I am looking for a formula that then clears the cells (B1 or C1) for the next entry (the next day/week).

2. A status formula that will state when A1 (quantity stock) is above the reorder level (d1) the cell (e1) will be green and say In Stock, otherwise if a1 is less than or equal to d1 then state Need to order, and turn cell e1 yellow.

If anyone can help I would really appreciate it!!! I would even pay for your time invested if I need to!!

Thanks!!

 

[tweedle]

Re: Inventory Formula

Greetings, and Welcome to the board!

Regarding Requirement#1; we would need to know which calculation is intended to be used.
And regarding the replacement of A1 via formula; in Excel terms, that's a circular reference; one would need to apply vba code (simulating the human entry) to make that work.


As to Requiremment #2:
one way might look like this: =IF(A1>D1,"In Stock","Need to Order")
with conditional formatting wizard that says something like:
If E:E contains "In Stock" then format to green
If E:E contains "Need to Order" then format to yellow

could also say
If A1>D1 then Green
If A1<=D1 then yellow

....

 

[cashchris2005]

Re: Inventory Formula

I reposted another thread showing an example of what i am trying to do. See inventory questions thread.

Thanks for the help!!