A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Stock control question



 
 
Thread Tools Display Modes
  #1  
Old February 10th, 2010, 02:53 AM posted to microsoft.public.excel.worksheet.functions
Rocket
external usenet poster
 
Posts: 21
Default Stock control question

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

  #2  
Old February 10th, 2010, 09:23 AM posted to microsoft.public.excel.worksheet.functions
Garreth Lombard
external usenet poster
 
Posts: 17
Default Stock control question

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

--

Thank you and Regards

Garreth Lombard


"Rocket" wrote:

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

  #3  
Old February 10th, 2010, 09:34 AM posted to microsoft.public.excel.worksheet.functions
FSt1
external usenet poster
 
Posts: 2,788
Default Stock control question

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

"Rocket" wrote:

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

  #4  
Old February 10th, 2010, 09:51 AM posted to microsoft.public.excel.worksheet.functions
FSt1
external usenet poster
 
Posts: 2,788
Default Stock control question

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" wrote:

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

"Rocket" wrote:

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

  #5  
Old February 10th, 2010, 10:21 AM posted to microsoft.public.excel.worksheet.functions
FSt1
external usenet poster
 
Posts: 2,788
Default Stock control question

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


"FSt1" wrote:

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" wrote:

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

"Rocket" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:07 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.