Help with formulas please!!

[cashchris2005]

<table id="post2623850" class="tborder" align="center" border="0" cellpadding="6" cellspacing="0" width="100%"><tbody><tr valign="top"><td class="alt1" id="td_post_2623850" style="border-right: 1px solid rgb(255, 255, 255);">Hello All,

I have a inventory question that I need help with.

Lets say cell:
A1=Starting Quantity in stock (Person Enters)
B1=Quantity in stock (Formula)
C1=Quantity used per day (person enters, then calculates, then cell clears)
D1=Quantity used per week (person enters, then calculates, then clears)
E1=Reorder Level (Person enters)
F1=Status (Condition formula)


P/E=Person Enters



<table class="MsoNormalTable" style="width: 428pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="713"> <tbody><tr style="height: 45pt;"> <td style="width: 62pt; border: 1pt solid rgb(120, 145, 110); background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="103">

[FONT=&quot]Starting Quantity[/FONT]​

</td> <td style="width: 61pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="102">

[FONT=&quot]Quantity in Stock[/FONT]​

</td> <td style="width: 73pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="122">

[FONT=&quot]Used Today[/FONT]​

</td> <td style="width: 70pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="117">

[FONT=&quot]Used This Week[/FONT]​

</td> <td style="width: 77pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="128">

[FONT=&quot]Reorder Level[/FONT]​

</td> <td style="width: 85pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="142">

[FONT=&quot]Status[/FONT]​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 62pt; border-width: medium 1pt; border-style: none solid; border-color: -moz-use-text-color rgb(175, 180, 135); background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap"> [FONT=&quot]P/E[/FONT]
</td> <td style="width: 61pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="102" nowrap="nowrap"> [FONT=&quot]Formula[/FONT]
</td> <td style="width: 73pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="122" nowrap="nowrap"> [FONT=&quot]P/E then Clear[/FONT]
</td> <td style="width: 70pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="117" nowrap="nowrap"> [FONT=&quot]P/E then clear[/FONT]
</td> <td style="width: 77pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="128" nowrap="nowrap"> [FONT=&quot]P/E[/FONT]
</td> <td style="width: 85pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap">

[FONT=&quot]Condition formula[/FONT]​

</td> </tr> </tbody></table>

What I need formulas for are
1. Subtract quantity used per day(b1) or quantity used per week (C1) from quantity in stock (b1). Then I want the total of this (a1-b1 or c1) to replace the quantity in b1 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 b1 (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!!
</td> </tr> <tr> <td class="alt2" style="border-width: 0px 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255);">
</td> <td class="alt1" style="border-width: 0px 1px 1px 0px; border-style: none solid solid none; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255) -moz-use-text-color;" align="right"> </td></tr></tbody></table>

 

[JamesW]

Hi there,

How do you determine which 'Used' cell to use, surely if you use some that day it automatically adds it to the 'Used this week' cell?

For part 2:

Cell F formula = =IF(B2>E2,"In Stock","Reorder")

Format -> Conditional Formatting -> Cell Value Is -> Equal To -> ="Reorder" (set to red and set the default colour to green)

 

[cashchris2005]

Hello James,

Thanks for the reply.... I will make it simpler and just say how can I have it be where they only enter how many parts were used per week.

Basically I know it will take 2 sheets, the inventory will be on excel sheet 1, and the formulas will be on excel sheet 2.

I want it to be that when the operator plugs in that they used 5 parts for the week, it gets deducted from the overall quantity that is in stock. This is then checked against the reorder level (which an operator enters), and determined if more is needed to be ordered, or are we instock.

If you have an email I can send you the inventory sheet i am working on, so you have a clearer picture of what I am trying to achieve.