Setup A Basic Stock Control Spreadsheet
Feb 22, 2009
I've set-up a basic stock control spreadsheet. Part of this requires updating prices of products. My approach was to use 3 columns A,B & C. C would show the current unit price of a product. Where there is a change in price this would be entered in A which would then result in a new average unit price to be calculated automatically in C. When ever this change occurs i need the then 'old' price which was in C to be shown in B aswell.
I've tried using various averaging formulaes but i'm thinking they were probably to basic or just plain wrong
View 9 Replies
ADVERTISEMENT
Feb 2, 2009
I have an opening balance colum for the month(I5), then a colum for receival stock for each week in the month + total of stock received for the month(O5), also a colum for delivered stock for each week in the month+ a total deliverd for the month(U5). I wish to then calculate the closing balance in (V5)
Therefore the simple formula =(05-U5)+I5 with the product dispalyed in (V5)
opening balance 3 units(I5) receivals =0(O5) deliveries = 3(U5) closing balance should equal =0 (V5). However the product displayed in V5 is incorrect and shows 6
What am i doing wrong? what formula will give me the correct answer to this equasion?
View 3 Replies
View Related
Jul 12, 2012
I am trying to create a basic stock quote reader.
I recorded what happens when I use the "from web" tool from the data tab. I then tried to modify it by allowing me to manually (with the inbox) put in the ticker that I want. When I run it, it does nothing. When I manually put in the ticker symbol for the company, it works.
For instance, I I were to replace
"URL;http://ca.finance.yahoo.com/q?s=" & TICKER & "&ql=1", Destination:=Range("$A$1"))
with
"URL;http://ca.finance.yahoo.com/q?s=ATVI&ql=1", Destination:=Range("$A$1"))
It would then work, but I would have to manually change it every time.
Here is the code
___________________________________________________________
Sub Macro1()
Dim TICKER As String
InputBox ("enter ticker symbol")
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _
[code]...
View 1 Replies
View Related
Apr 11, 2007
i am trying to create excel system with stock control, but i dont want to use VBA. i want to use MAcro or farmulas.i want to know how many quantity is left in the stock
View 6 Replies
View Related
May 3, 2008
I would like to know if it's possibale to use VB, to auto manage these levels
IE, 1 box asks for the part number 2nd asks you how many 3rd/4th boxes are "+", "-"
When + or - is pressed the part number will automatically add's/subtracts that part level. I had 10x abcde's I enter 2 in "How Many", then press "-" it will then adjust the level from 10 to 8
View 5 Replies
View Related
Nov 27, 2009
i have been tasked with making an inventory stock control system which i know would be better on access but i dont know how?
Would anyone have or lead me in the right direction to a template/file to get me started
I need to have the ability to book out stock to particular jobs etc and keep a running totals on stock value etc...
View 8 Replies
View Related
Apr 17, 2007
'The following code creates a new commandbar name Visual Basic Editor, which on event delete the VBE module1. How can I tie the built in visual basic editor commandbar to this.
Sub bar()
Set mybar = CommandBars("Tools").Controls("Macro")
Set mycontrol = mybar.Controls _
.Add(Type:=msoControlButton)
With mycontrol
.Caption = "Visual Basic Editor"
.FaceId = 1
.OnAction = "MySub"
End With
mycontrol.Visible = True
End Sub
Function mysub()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With
End Function
View 6 Replies
View Related
Jun 26, 2008
I am trying to set up a costings sheet for my other half. So far I have worked out his costs for this basics i.e. van etc. man power, days, mileage. He now wants to be able to enter his miles to a job, man power, days etc, Then to show total invoice cost and show profit i.e. if miles need to be changed.
View 3 Replies
View Related
Dec 23, 2010
I have set up a spreadsheet but need to add a tick box, I just need to tick it once the item has been completed.
View 6 Replies
View Related
Oct 1, 2008
I'm not sure if this is something that can be done by macro, I have a database setup using a spreadsheet in Excel. The first column is a list of customer names, that can be filtered by customer name. If I filter the column, using for example the name Jones, is there a way to add up all the units of the product that they have purchased?
View 9 Replies
View Related
Mar 7, 2013
I have a small spreadsheet setup for my job where i have to input the time on arrival in L3 which is linked to a NOW() formula. this is it (=IF(L3="",NOW()
View 9 Replies
View Related
Sep 15, 2008
I'm trying to put together a basic spreadsheet to track offensive plays for high school football. I'd like a formula that will change the LOS (Line of Scrimmage) to reflect the yards gained on the previous play. The problem is that you'd probably need negative numbers up to 49, and then back down to 0.
View 10 Replies
View Related
Jan 12, 2007
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
View 4 Replies
View Related
Jan 15, 2010
I'm creating a basic Dart scoring spreadsheet, and its been going absolutely fantastic, but then I was asked to create the Sets and Legs, thats when I got stuck.
I'm looking for a formula (or anything) that will add a Set when the Legs reach the number 3, and then again but make it 2 if that player gets another 3 legs. If you know darts you will understand what I mean.
View 9 Replies
View Related
Mar 27, 2009
I have two spread sheets from one of our suppliers, one was issued in 2008 and the other 2009. The newly issued sheet has about 400 extra items. How can I identify these new items of stock easily. I have attached both sheets for you to look at.
View 5 Replies
View Related
Nov 16, 2011
I am trying to set up a spreadsheet to calculate margin on stock positions.
There are 3 tiers, based on number of shares. My example:
400 shares in held in total.
1st 200 shs margin at 10%.
next 100 shs margin at 20%.
everything above 300 shs (ie 100) margin at 30%.
The 400 shs may be made up in any number of ways, (ie lots of smaller amounts) and i need a formula to give me the margin amount on each position, flexible enough for when positions are added / removed.
I've been looking at SUMPRODUCT but can only seem to make it work for the total, not each individual position.
View 9 Replies
View Related
Jan 3, 2014
I would simply like to know how to create a basic ranked leaderboard through EXCEL or Google Spreadsheet, which i will later on implement on a community forum. I would like 2 entries for the leaderboard column titles, being:
Column A: PLAYERS
Column B: POINTS
Then under A i will be typing all players manually, and similarly for B with points.
View 3 Replies
View Related
Jan 8, 2010
We run a fairly small start-up company and I would'nt mind knowing if it was possible to have some kind of stock control system on 'worksheet1'. On 'worksheet2' have some kind of 'Till Style' GUI interface where you can input the products the customer purchased. And then maybe of 'Worksheet3' have a printable recipet which you can hand to the customer.
Of course the idea would be for what-ever is purshased via the 'Till-Style' GUI on 'worksheet2' to alter the stock QTY on 'worksheet1'.
View 9 Replies
View Related
Aug 7, 2012
What is the easiest way to have a cell update with real time stock quotes for a particular stock?
View 4 Replies
View Related
Jan 8, 2014
I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.
I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?
This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?
View 7 Replies
View Related
Jan 16, 2010
Hi all im having a tough time trying to figure out a way to select a range (which will be the entire row )based on a condition that will be data selected from a combobox and display that range in a userform with spreadsheet contol 9
what i have so far is a worksheet with all the data.
i have a combobox on a userform that is prefilled with data from a specific column
every thing is working except when i select the entire row how do i change that to a range that i can use in the spreadsheet control 9 that is added to the userform.
View 11 Replies
View Related
Oct 13, 2009
How do I get the handle for a combobox on a spreadsheet? I have tried ChildWindowFromPoint and RealChildWindowFromPoint without any luck.
View 9 Replies
View Related
Jun 13, 2013
I am working an a userform. I want people to add records to the spreadsheet thru the UserForm ONLY, meaning that if they go straight to the spreadsheet they wont be able to edit anything.
View 1 Replies
View Related
Feb 1, 2008
i have a spreadsheet that uses control functions to run vb code. i am trying to make things as user friendly as possible for all of the employees. i am currently working on a control to clean out any rows that the first cell has a value of "N/A" or is left blank. i have the code for the blank cells, but cannot figure out how to add the "N/A" cells.
here is the current
Private Sub CommandButton2_Click()
Dim rng As Range
On Error Resume Next
Set rng = Columns(1).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub
View 9 Replies
View Related
Feb 26, 2007
I've been working on a spreadsheet to control the 'booking diary' at work and linking it with the movements in and out of our 'chambers'
If you look at Column L & M, they are the volumes and column N is where the 'pallet' is coming / going to .
If you look at line 149 and below I've tried to translate these 'volumes' into movements by time but as you can see it is not to totalled up correctly during certain times.
For example line 158 should total 40 and line 187 should total 80.
Can anyone work it out for me as this happens on numerous lines
View 12 Replies
View Related
Jun 16, 2009
I have a spreadsheet control inside of a userform. I can generally access this spreadsheet and do what I need to do with it. My problem is that I need to monitor it for the cell change event. I normally accomplish this with:
View 4 Replies
View Related
Oct 10, 2013
I have a userform with a textbox and would like the user to type inside the textbox which in turn send the text typed to a cell on my spread sheet say sheet 1 cell ref A1. I am writing the following into the control source Sheet1!A1 but the control source does not except this. I am using excel 2007 .
View 2 Replies
View Related
Jun 3, 2006
I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?
Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............
View 5 Replies
View Related
Mar 4, 2014
Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?
It might be clearer to explain by a dummy code example:
[Code] ......
I'm seeking what I would need to replace Line1 with.
View 11 Replies
View Related
Jan 9, 2008
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
View 9 Replies
View Related
