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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|