Coding To Order Stock

May 8, 2014

I am looking to create coding that once stock gets to a particular level a text box pops up to alert the user that more stock needs to be ordered and ideally I would like once the user selects ok for it to take them directly to the appropriate email template.

However, I don't want the text box to come up while we are waiting on the stock to be ordered.

i.e our current minimum stock level before placing our next order is 10,000 units. Order is placed and can be received within 10 - 14 days. During this time our 10,000 units will be used but I don't want an alert to pop up to remind the user to place an order as this action will already have been carried out.

View 1 Replies


ADVERTISEMENT

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

Office Stock Order

Feb 17, 2007

My organisation has ten small offices within a ten mile radius.

Most of the items to order are office supplies, along with order forms, catalogues, brochures and pro-formas.

Each office does not order the same items.

I would like to make a ‘generic’ supplies order in excel.

Column A = ‘in house’ code
Column B = Description
Columns C to G are size, colour, pack size etc.
Column H = quantity held
Column I = quantity required to hold
Column J = amount to re-order.

I would like Excel to calculate

Amount to bring stock to hold - “=I4-h4” (in J4)

If Current Stock is more than Stock to Hold then ‘0’ is in J4, “=IF(H4<I4, I14-H14,0)” (in J4) I think.

If H4 is left blank then J4 to show “0” or ‘Blank’. (experiments have given J4 a figure of 25)

I have tried putting two formulas together, but Excel tells me I have ‘too many arguments’.

View 9 Replies View Related

Macro With Formulas - Subtracting Next Order With Current Stock

Mar 25, 2014

I would like to use this database list I export from another program to excel format. What I want to do is to change in the C column.

The value under Akt.saldo is how much of a given article we have in stock (saldo). And the Rest. in column B is the reservation, how much of that article thats is going to be shipped at that day. ex 14161 year(14) week(16) day(1).

So as you can see in column C the Rest. value never subtracts from the stock. What is want is something like that below.

Here is just selected all the values under stock and moved to the next column. Then used white color to hide it.

Here i subtract the hidden value with the reservation to be able to see how much is left after that order.

And the same here. Subtracting next order with the current stock.

How do i do this with a macro? The list is changing everyday.

View 2 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

Macro To Set The Order In Which Worksheets Should Be Printed (order Is Changing Daily)

Nov 29, 2008

I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:

I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?

Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...

Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).

Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.

View 9 Replies View Related

Re-Order Columns Based On Column Order Of Another Worksheet

Mar 1, 2008

I need a way to re-order an excel worksheets columns based on another worksheet.

Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.

How can i reorder the columns without physically having to cut and paste the columns to match?

I have attached a sample spreadsheet.

You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.

View 3 Replies View Related

VBA Coding

Mar 18, 2008

I am trying to code a macro to run down through a column and hide the row if it finds a cetain marker (i.e. the letter "n") in the column, and then move onto the next one.

View 9 Replies View Related

Stock Levels

Mar 27, 2008

how to go about actually doing it,

For an old project which was a till system i had two sheets,a data sheet and till system sheet , the data sheet contained :

example:
column A - numbering of the data (to be used with V Lookup)
column B - product name
Column C - price

This would then be replicated in the next 3 cells for the next data category.

Till system then had a combo box which had a cell link on the current page and data from the data sheet and then i had a price column next to i (containing V Lookup formula) the price then changed depending on the choice in the combo box.

I want to incorporate combo box's in to this new project. If i can then get some kind of stock thing i intend on then using conditional formating to colour code stock levels to show severity of needing to order etc..

View 14 Replies View Related

VBA For Stock Allocation

Jun 13, 2008

Let say I have 5 shops: A, B, C, D, and E

They request for stock replenishment from warehouse as follows:
A: 4 pieces
B:2 pieces
C:3 pieces
D:3 piece
E:3 piece

But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock

how to write the scripts to do this allocation.

View 9 Replies View Related

Stock Turnover

Oct 12, 2006

Question
Is there a formula that calculates my desired result which is row 5? I tried creating a formula in row 6 but it fails.

What I am trying to calculate is in week, if I have inventory then how many weeks does it cover?

For Instance, Week 1 I have inventory of $39,120 which covers until 4 weeks and then calculate total of wk1 to 4 and subtract it from inventory value 39,120.

The resultant from this calculation is divided by week 5 and expected production in order to derive the proportion covered in week 5
Please refer to my workbook

View 9 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

Coding Months

May 22, 2007

My spreadsheet calculates the date on a daily basis.

I would like to create a code in my spreadsheet which uses the year and month in question.

First, 2006 is is designated as a "3"
2007 "4"
2008 "5"
2009 "6" etc

Don't worry about anything prior to 2006.

Next, the months are represented by single digits. So, January = 1, February = 2, March = 3, April =4, etc. September is 9, and October = 10 but all the numbers over 9 have to be reduced to a single digit, so October = 1+0=1, November =1+1= 2, December =1+2= 3.

Now, the formula for the Month is:

Year + current Month

So, May 2007 = 4 + 5 = 9
June 2007 = 4 + 6 = 10 = 1
July 2007 = 4 + 7 = 11 = 2,
January 2008 = 5 + 1 = 6, etc

View 9 Replies View Related

Coding A Macro In VBA

Apr 3, 2008

Is there a way of coding a macro in VBA that allows you, with one macro, to enter text in one cell and at the same time enter a value to the cell on it's right?

I don't want to name the cells, but I wondered if you could add to the code a piece of code that basically said "Select the cell to this cell's right and add the value x".

View 9 Replies View Related

Optimizing Coding

Jun 3, 2008

I have made this vba to go out and look at 2 different locations which contain simular folder names.

The script then writes the folder names in Column A, followed by the Size of both the folders from the 2 locations. The script is comparing the second location folder name to what the first process put in Column A so the folder names will be aligned with the file sizes. It is also converting the file sizes from bytes to the most appropriate one.

The problem I am having here, is once I put the second part in for the second folder, the script is either taking forver (More than an hour) or is freezing. So I am not sure if I put it in a loop somewhere or what exactly is happening. I am just wondering if I can get some help troubleshooting and optimizing this code....

View 9 Replies View Related

Lock Vba Coding

May 1, 2007

How it is possible to lock the VBA coding and Modules? I mean when Some one want to view VBA coding the Password window should not appear, istead of that a message box appears with message something like "Not viewable"

View 9 Replies View Related

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

Generate Vba For Opening Stock?

Apr 10, 2014

PFA my inventory managment sheet. In transections sheet i am entering my stock in and out details. I want to generate monthly statement for particular item. I am able to get issue and receipts but not able to get opening stock on the particular day.

View 3 Replies View Related

Automatically Update The Stock

Feb 10, 2009

I have attached a sample of our worksheet (GTS807) used to create quotes and generate jobsheets etc once they are orders. To ensure we have enough of a size in stock I want to carry out a check against the stocksheet (stock) which I have no problem but I cant get it to automatically update the stock once a quote becomes an order and removes the quantity from stock!

As you see the balance of stock in shown in AA13 for "115270" but is there a code to find "115270" on the stock sheet and copy the new balance back there?

View 2 Replies View Related

Assign Stock To Orders

Apr 10, 2008

I need a macro that looks at the earlier orders first and assigns inventory to them. Keep assigning inventory until either there are no more orders, or the inventory is depleted. I have tried to figure this out using formulas but it never seems to work out correctly. Here is a little sample I made to show what I am trying to do.


Item# Date Quantity Order Quantity Committed

Item# Quantity in Inventory 1 1/1/2008 18

1 21 2 1/5/2008 23

2 300 3 1/10/2008 10

View 13 Replies View Related

Historical Stock Prices

Jul 24, 2008

how do i download historical stock prices for approx a hundred stocks i.e what code would suffice?

View 9 Replies View Related

Worksheet Stock Data

Nov 3, 2008

here is what i have but i want it to adapt it you use any company by using a list of companys in a worksheet "StockData". So the company names start in "A2" and go down the column. So i want to run the download then for the modual to go to the next company in the list without having to have a sub() for each company....

View 9 Replies View Related

Function - Stock Weeks

Aug 7, 2009

I have limited experience at writing functions and I can't seem to get this one to work, in fact I think I am way off. I wan to calculate weeks of stock on hand assuming an opening value of stock and assumed sales levels.

For example 5000 opening stock on 1 Jan and sales of 1000 in Jan, 2000 in Feb and 4000 in March would be calculated as:

52/12*2+(5000-2000-1000)/4000*52/12 = 10.8 weeks

I have written an if formula to calculate this however it is cumbersome has limitations due to the number of if's that can be entered.

View 9 Replies View Related

Automatic Stock Updates

Oct 4, 2006

For a project, I need a way to automatically have updated stock values in a worksheet to work into various other formulas. I tried the HYPERLINK formula to the individual stock reports, but it wasn't specific enough to simply get the current value of the stock. Is there a way to narrow that formula down to get only that number, and still have it update every time I open the worksheet?

View 2 Replies View Related

Annotated Stock Chart

Jul 24, 2007

Attached I have my data - there is stock price information (monthly basis) as well as analyst upgrades / downgrades / or initiations.

Is there a very neat way to create an annotated stock chart for these ocurences - perhaps using symbols along a line chart that pots out the monthly stock price on the y axis and the months on the x axis.

View 2 Replies View Related

Dropdown Box Coding To Another Cell

Dec 24, 2012

I have a drop down box with the 10 different shifts we run here at my work. Ie - Daytime (0700-1500), Nightime (1500-2300) etc. I have these on a drop box as stated earlier (Cell 6) - and want to code a selection from within the box, to effect Cell 7 with the approriate hours worked depending on what I selected in Cell 6.

View 2 Replies View Related

Dynamic Range In Vba Coding

Nov 2, 2008

I'm trying to write a code which would place formulas in the row just below the my range (normally a matrix of figures) to sum the column in that just above that. Here's what I've got so far but it doesn't work.

View 6 Replies View Related

Coding Multiple Cases In VBA

Mar 24, 2009

I am writing VBA code to do the following:
IF A1 > 50 then print array C1:E7

I have this code and it works.

View 14 Replies View Related

Deactivate Coding Not Working?

Sep 3, 2009

I have placed the following code in my spreadsheet but it's not working!

the code after option explicit (now at the bottom) works, (thanks to Andy on here!) but all the disabling the right click and cut/copy/paste etc doesn't.

View 6 Replies View Related

Coding For Changing Worksheet Name

Oct 26, 2009

when the accounts exported to excel, all the tabs shown in the file are generated. However, i need to change tab '3' to tab '10' name to the name stated in tab 'menu'. eg tab 1 need to be renamed as 'BB' (not BB/Bunut), tab 2 to 'GEN' (not GEN/General), etc. All must be capital letter.

View 5 Replies View Related







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