I have made an Excel illustration to explain what I would like to embark on.
I am not sure of the code, but if there is not an idea, I may see if I can find some idea of the code.
What is the easiest way to have a cell update with real time stock quotes for a particular stock?
View 4 Replies View RelatedI 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?
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
How to create a code formula to calculate the arc length from a given chord length?
If you know the radius of the major circle.
Say the chord is 50mm and major circle dia is 72mm (radius 36mm)
arc from chord.jpg
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
I am trying to get a formula for material quantities
What I want to do is
If length is less than 3mts I require 2
If length is more than 3 meters than I require 3 and 1 more for every 3mts after that
e.g:
2mts = 2
3mts = 2
4mts = 3
6mts = 3
7mts = 4
9.1mts = 5 and so on
create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e
if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
basically I want to be able to keep track of how much vinyl material I have left after each order.
The process would be - When I order a roll of vinyl material I would input the colour ordered and cm ordered by selecting from drop down lists in the 'Vinyl Tracker' sheet. When a customer makes an order, I would select an item, size and colour from drop down lists in the 'Orders' sheet. Depending on what size is selected in the 'Orders' sheet, I would then like Excel to automatically update column 'Cm Remaining' in the 'Vinyl Tracker' sheet, however using the smaller number in the relative size column from 'Item Sizes' sheet.
E.g. If we take the first order:
World Map
Medium
Black - (M)
This would then refer to cell M5 in the 'Item Sizes' sheet (as 43.35 is less than 90).
I would then like the number which is retrieved to be taken away from the relevant cell in the 'Cm Ordered' column, depending on what colour was chosen in the 'Orders' sheet.
To make matters a bit more complex, obviously when any of the numbers in the 'Cm Ordered' column in sheet 'Vinyl Tracker' is 0, I will re-order the same vinyl roll and insert it into the sheet as per usual. How can I make it so that any new orders will take away from the latest instance of the same coloured vinyl?
I use CAD software that generates Bills Of Material. I cut & paste these to an Excel template that has column headers in row 3, for example:
U3 = Item name
V3 = Manufacturer
W3 = Reference_item_name
X3 = Reference_item_ID
Starting from row 4, I would like to add the content of columns V, W and X to column U, separated by comma's. No superfluous comma's should be added when columns are empty. It would be nice to have a macro that uses the row 3 column names, so it still works if someone changes the column order.
I have a Spreadsheet with various tabs.I want to :-
1.A Userform to add records into "Material Indent"tab.
2.Secondly,transfer rows button on Userform to shift particular rows on entering Reel no. and date to "material Usage"job desired.xlsmtab.
I need to sort the material data based on the material rank but i can't use the 'sort/filter' function. Therefore, I used the VLOOPUP function. For some reason the vlookup formula is not working could you let me know what is the problem? see attchment.
View 2 Replies View RelatedWhat i am trying to do is to to determine the common material that is
used among different model do product in a product family. I have the
column C the various part number for the product family. Each product
model is made up of different combination of the parts.
In I3:U3 i have the model number for each product. Under each are the
combination of various part that make up each model. What i need to do
is in column G conditional formatiing that if all the different model
use a particular part (part number). The respective cell in column in
the row will be color. This will help me to determine what are the
parts that are common to all the product.
Column C Column G Column I .........................Column U
Part no Common Product 1 Product 2 Product 3 Product 4
12-1234-56 no color 1 4 0 6
13-2345-45 color 2 3 2 2
14-1234-56 no color 0 2 4 2
14-1234-56 no color 0 2 2 2
I have a material spreadsheet list that contains multiple entries of the same parts throughtout the sheet. How can i get it to total quanities needed by part numbers and consolidate it to one row instead of multiple rows. quantities are in column c and part numbers are in column d and descriptions in column e.
View 3 Replies View RelatedI would like to create Bill of material from single table. I need to select multiple parameters, to expend it till the lowest level, so I will try to explain:
I have one table (ODBC) and there's all data we need. Parent number col A, Item number col B and quantity col C. First level I select main Item number A1234 (this is only thing that I should choose, everything else should be automatically), I get table with all items that are parent A1234 (let's say 10 items). Now I need to look again one level lower in same table for items that have Parent item in list of those 10 items listed earlier (let's say 30 items) and multiply their quantities with quantity of their Parent Item (total qty could be in column D). Then one level lower for items with parent items in those 30 and so on and so on. So when I choose main Item I would like to get table like below (take notice that real table has over a 100.000 items, but I want to show only Bill of material for the main item till the lowest level).
Parent Item
Item
QTY
Total QTY
A1234
B1111
5
[URL] ...
I'm flexible and ok to use VBA, SQL, Excel functions, multiple tables (how to select multiple parameters??)
I am reposting this because I do not think I did a very good job explaining what I am trying to do.
I am at a total loss on how (if it is even possible) of how to do this. So what I have done is tryed to break down each step. If someone could even get me started in the right direction many of the steps are redundant and I could work on that part myself.
I am attempt to create system to match parts that are alike in a single project, so I can create a material ordering list. This is just one step in the process (the hardest one) I will take the returned data and use it further in the process to create the actual material list. I have 2 worksheet of Data "PartsNeeded" and "PartsAvailable" with a 3rd sheet "PartsFilled" as my report Sheet.
Attach is the sample data along with a "notes tab"
I have a list of products that have individual item numbers that share some common sizes and some common materials and are ordered in different quantities. When it comes to re-ordering the raw material to remake these items, I need to know how many of each item in the same type of material and size, need to be made. The attached spreadsheet shows more clearly what I am probably failing to explain! I have tried various combinations of VLOOKUP combined with MATCH but I can’t get anything to work – I guess I’m getting a little punch drunk here having spent the whole morning trying to find a solution!
View 5 Replies View RelatedBelow is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
Would a macro make the task more automated?
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
View 2 Replies View RelatedI would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies View RelatedI have been working on a workbook that uses as a source information on Sheet 1.
Sheet 1
Each item reports a number of different details from column A to column BY.
Each row is for a different item.
25 rows of information.
Sheet 2
This is my master formula sheet, the one I've been using to create the page that reports and formulates information on a single item. The source for this sheet is taken from the cells in Sheet1!Row2.
Now that I have the sheet pretty much finished, I want to paste the same report on each succeeding page for a total of 25 sheets. I can copy Sheet2 and paste into each successive sheet, but then I need to change every single formula so that where it said AT2 it changes to AT3, where it was H2, H3, etc., from A to BY.
Problem: Can I use something that will automatically take the information for succeeding sheets and take as its source the succeeding row from the source page?
I don't know that I'm describing that sufficiently, so:
Sheet 3 would take as its source Sheet1, row 3.
Sheet 4 would take as its source Sheet1, row 4.
Sheet 5 would take as its source Sheet1, row 5.
Sheet 6 would take as its source Sheet1, row 6.
Etc., etc.
In the past I used the find/replace function, but this sheet seems way too complicated for that. As an example, one cell alone on my master sheet is
=IF(ISBLANK(ConnectMLS_export!S2),(IF(ISERROR(LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15))),"",LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15,1)))/43560),(ConnectMLS_export!S2))
so I would need to change S2 to S3 everywhere it appears, but there are probably more than a hundred places on the sheet that I'd have to manually change 2s to 3s, etc., for 25 sheets, which is why I'm wondering if there isn't a better way than going cell by cell, sheet by sheet.
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..
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.
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
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 RelatedI 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?
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 RelatedI 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.
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?
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