Stock control question

[Rocket]

Although I have used Excel for years I have never had the need to use a
complicated formula.
I am trying to set up a stock control spreadsheet where multiple items are
deducted when I click on one cell.
What I am trying to do is each time I click on cell A3 "Item" all the
amounts in C will be deducted from D and the difference between B and D will
show in E


A B C D
E
1 Normal Amount In stock To
be
2 Stock level per item
ordered
3 "Item"
4 Side frame 200 4 200
0
5 Bottom bracket 100 2 100
0
6 Top bracket 100 2 100
0
7 Wheels 400 8 400
0
8 Axles 200 4 200
0
9 Springs 800 8 800
0
10 M5 Dome nuts 800 8 800 0
11 M4 Set screws 1600 32 1600 0

I don't know whether this is possible but if anybody has an answer (apart
from "your joking") it will be appreciated

 

[Garreth Lombard]

Hi There Rocket,

I am trying to understand this question as best as I can. So here goes....

"amounts in C will be deducted from D" ::::: Formula for C will be =sum(C3-D3)

Next formula.......
"he difference between B and D will show in E" ::::: Formula for E will be
=sum(B3-D3)


Please let me know if you would like this automated in any way.
You specify that you want to click on A3 and the results should show in the
selected fields. The formulas stated should give you the result without
clicking anything and shuld update when your totals change.

Let me know if this works for you, if you are unclear about my response then
we will take another route with it

Hope to hear from you soon

Regards

Garreth

 

[FSt1]

hi
sorry but formulas don't work that way. formulas return values to the cell
in which they reside. they can not perform actions such as the ones you
describe.
to do what you want, you will have to use VB code. not sure if you want that
but...
your example sort of skewed around a bit in my interface so i made some
assumptions.....
normal stock level is column B
amount per item is column C
To be ordered is column D
there are no blank rows in your data(important)

there is no click event in sheet code but there is a before double click
event so the code is triggered to run by double clicking A3.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim rnsl As Range
Dim rnsld As Range
Dim rapi As Range
Dim rstk As Range
Dim rtbo As Range
Dim br As Range
Dim lr As Long
Set rnsl = Range("B4")
lr = Cells(Rows.Count, "B").End(xlUp).Row
Set br = Range("B4:B" & lr)
If Intersect(Target, Range("A3")) Is Nothing Then
Exit Sub
Else
'Do While Not IsEmpty(rnsl)
For Each cell In br
Set rnsld = rnsl.Offset(1, 0)
Set rapi = rnsl.Offset(0, 1)
Set rstk = rnsl.Offset(0, 2)
Set rtbo = rnsl.Offset(0, 3)

rstk.Value = rstk.Value - rapi.Value
rtbo.Value = rnsl.Value - rstk.Value

Set rnsl = rnsld

Next cell
End If

End Sub

careful. in my interface, the line Private Sub... wrapped.
to install the code, right click the sheet tab and from the popup, click
view code.
with will bring up the VB editor. paste the above code in the code box. (big
one, far right, above the immediate box.)

if you are new to macro, see this site for general info on getting
started......
http://www.mvps.org/dmcritchie/excel/getstarted.htm

post back if problems.
it's late in atlanta. i will check back tomorrow.

Regards
FSt1

 

[FSt1]

hi
forgot to mention. if might be a good idea to clear the amount per item
after the code runs... to prevent accidental double dipping. if you want
that, add this line to the code....

rapi.ClearContents

After line rtbo.Value = rnsl.Value - rstk.Value

and before line Set rnsl = rnsld

the formulas provide by Garreth would work but not by double clicking A3.
the numbers would change as you entered the data.
my code works too. tested.

so i hope i didn't misunderstand.

Regards
FSt1

 

[FSt1]

hi
sorry. i should have looked at garreth's formula closer. they will work but
not in columns c and d. you would have to create 2 new columns to house the
formulas

regards
FSt1