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

Stock Control Spreadsheet

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

Create A Basic Stock Quote Reader?

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

Stock Control Without Vba

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

Stock Control With Controls On Worksheet

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

Making An Inventory Stock Control System

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

Visual Basic Editor Commandbar Control

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

How To Setup A Spreadsheet

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

Spreadsheet Setup - Creating Tick Box

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

Macros - Database Setup Using A Spreadsheet

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

Spreadsheet Setup - Conditional Format Formula

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

Put Together A Basic Spreadsheet To Track

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

Drag From Treeview Control To Spreadsheet Control

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

Basic Dart Scoring Spreadsheet

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

IF Formula - Identify New Stock Items In A Spreadsheet?

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

Set Up Spreadsheet To Calculate Margin On Stock Positions

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

Create Basic Leaderboard In Excel / Google Spreadsheet

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

Small Scale Stock Sheet / Epos Style Spreadsheet

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

Cell Update With Real Time Stock Quotes For Particular Stock

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

Stock Order Sheet To Be Created From Stock Inventory

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

Spreadsheet Control With Range

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

Get Handle To A Control On A Spreadsheet

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

Spreadsheet Read Only - Control With UserForm

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

Spreadsheet That Uses Control Functions To Run Vb Code

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

Formula- Working On A Spreadsheet To Control The 'booking Diary'

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

Spreadsheet Control (monitor For The Cell Change Event)

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

Excel 2007 :: Textbox Control Source Linked To Cell In Spreadsheet?

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

Using VBA Code In Visual Basic: Develop A Stand Alone Program In Visual Basic

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

Avoid Hard Coding Control Name Inside Control Event Procedure?

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

Select Next Control / Cell On Worksheet After Enter In Control

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







Copyrights 2005-15 www.BigResource.com, All rights reserved