I have a sheet which is just a list of product codes and a sum of every order placed for each code. i.e.
Sheet2 AB1Prod codeOrdered2123100345650478960
then I have another sheet which is the orders placed by our customers. These orders are to go out in 6 periods each of 2 weeks.
For various reasons the amount ordered each time won't match the periods.
What I need to do is look up the quantity ordered and then allocate the ordered quantity to the periods carrying over the remainder to the next period and so with the table above the result would be
I'm developing up a spreadsheet to conduct a feasibility on a 50 unit townhouse development and I was wondering if there was any way of allocating costs across the project without physically typing in the data in each month? (96 months in cashflow!)
ie: - In a row headed "Roads & Drains" can I allocate the say $500,000 worth of works across the months by using a formula or other methods?
My current method is very time consuming when I want to conduct a sensitivity analysis,
What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?
I have project's start date and end date and total cost. I want to spread the total cost across month on top using the total number of days[calculated using the end and start date] that project will run and the number of days that project is in that month. I have highlighted the area in yellow where I want formulas to work.
I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.
The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.
I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.
as there are 600 names and some can appear up to 4 - 5 times!
i have created one text box so that user enter the data to search by giving Message code. My concern is
From the example,
If the user enter 789 in the text box, and click on te search button( On user form) then it should dynamically create displays 2 occurance in each text box.i.e if the 789 found in 1 col then it should create 4 test boxesto display Program name data and 4 text boxes to display Error Description side by side. if not text boxes if will be if we display in lables also.
I am using Microsoft Excel 2003. My question is about calculating time. 1 hour + 1 hour and fifteen minutes would equal two hours and fifteen minutes. Using Microsoft Excel 2003, let's say I am using cells A1, A2, A3 and A4.
A1 will be 1:00 for 1 hour A2 will be 1:15 for 1 hour and fifteen minutes A3 will be my total for adding cells A1 and A2 and the answer will be 2:15 for two hours and fifteen minutes.
My specific questions is: Would it be possible for me to have the fifteen minutes (0:15) from the two hours and fifteen minutes (2:15) automatically carry over to cell A4 or cell A4 of another worksheet without having to type in 0:15 or having 2:15 appearing in cell A4?
I have 2 subs routines in a mod. I have declared the two variables at the top of the sub. However when I call the second sub the variable's are not passed along.
Here is an example script. All in one mod. I have taken out the junk in between to help edit the problem.
I'm looking to pass the same bnumber and dnumber to the secound sub.
below fig 1 is a part of a worksheet we are using for Stock. The idea is to track stock write downs on a yearly basis. Where I am struggling is a formula to carry the stock balance and value forward if there has been no movement in a given year.
The enteries in green (fig1) colums "Stk Forw" & "Car Fwr" represent how I would like the info to look.
Is there a formula that could look for the last entry in "Stk In/Out" if the column "YTD to date" is empty and carry that value forward to the next row.
I Would also need a formula to sum the Total & Car Fwr colums as one total.
What im trying to do is to be able to run the same macro but start on the last cell that I had selected and work on down the sheet until the end. What im also trying to do is to be able to post into next avail line on sheet 1 so it dosent overwrite.
I created a time sheet for employees which calculates the total wages each week. I copy the worksheet to create a new time sheet for each week. I want all subsequent time sheets (worksheets) after the first one to calculate the total wages to date by adding the total wages of the current sheet to the total on the previous sheet. If for example I am on Week 2, I know how to reference the wages cell from the previous week by using something like ='Week (1)'!M28, but when I copy the Week 2 sheet to create Week 3, the new sheet still references Week 1. I don't want to manually change the sheet number each week. How can I make it reference the previous week automatically?
its a simple spreadsheet that suppose to track a usage of items that were taken out of inventory, but at the same it needs to be printer friendly (that's what's causing all the problems),
it got messy when I had to carry over from previous row, I had to manually enter under "Quantity ch." in order to have correct amount under "Left" column, (see row #4)
I think I can eliminate whole "Carried over" column if I create a formula that will enter data into B4 only if J3 has a number, I also need to copy the same formula's down each column,
I have multiple invoices for ordered products style number and quantity, these invoices are keep adding as orders are coming. I need a separate work book where all items summed. shows style and summed quantity from all invoices.
for example wb1
style quantity or12 5 or34 2 or45 4
[Code]...
wb3 same style numbers but maybe not in the same column or row, wbs are keep adding as orders are placed.
formula on the attached sheet. I need to update it now. The strengths are going to stay the same, but the weaknesses (purple headers on spreadsheet) only have to show up if the value associated with it is 3 or greater. (So traits with quantities of 1 and 2 will not show up as a weakness)
I have attached a spreadsheet wit desired results.
I want to match the quantities of buy and sell appearing the same line but in different columns based on FIFO. In case of mismatch, the excess quantity should be transferred to a new line below the same column in which there is a mismatch.
I want to input pick 3 (3 numbers) into a cell and with the assigned SUM to that cell to add +123 to the 3 numbers I input, but how do I get the answer not to carry over?
For pick 3 games, the numbers are; 0,1,2,3,4,5,6,7,8,9 (ten total).
When I add +123 to 987 I get 1110. I don't want that. I would like it to show 010 instead, lotto numbers no carry over.
I'm looking for some ideas on how I can improve my invoice I have on excel,currently I have a spread sheet made to look like a standard invoice that prints on one page.
the problem is the more lines the invoice has the smaller the righting gets as it only prints one page.
how can I get my invoice to look nice, still automatically add up all the lines but go into two pages?
I've tried splitting the invoice but it looks professional.
I have been trying to work with to get my stock table classified into 3 different stock statuses and it comes out, it does not work I have attached the table as it is, the formulae I wanted to use and the end result I would like to have.
Below I have attached a sheet for keeping track of the number of cables a person produces per day using Drop-Down list. I would like to have a totals area automatically calculate the corresponding cells. I started the Totals area in column AF.
I need to sum a column when the quantities meet two criteria. The name in one column, and the date in another column. Probably an easy formula, but new at this and having trouble.
I need to sum up the batch quantities for a date with variance one... but it doesn't work... I suspect that I'm using wrong formula, it should be not SUMPRODUCT...
when I tried to use just SUM, it adds all the quantities in the colomn.
I have a Bill of Materials (BOM) that has been flattened from a database into an Excel format. There are many BOM levels and I need to multiply the quantity of a component by the quantity of the "parent" it reports to. I have attached a sample file with raw data and the expected result (calculated manually). My goal is to create a formula that calculates the "Ext. Qty" column in my expected result. EXT_QTY.xls
Here are some of the rules for the raw data structure:
1. First BOM level at very beginning is always "1"
2. All children of BOM level "1" have a BOM level of "2"
3. All children of BOM level "2" have a BOM level of "3"...
4. There are potentially many BOM levels (approx. 15 levels for full set of data)
5. All of the BOM level "2" items are children of the most recent BOM level "1" assembly and there will be multiple level "1" items, level "2" items, etc.
6. Data is always sequential so the children (and grandchildren) will always come after the parent
I have three values. The Quantity of items, the cost per items and the cost for the entire order. In some instances I want to give my clients items for free so I need to lower the cost per item in order to keep the entire order cost the same.
In order to ensure we are on the same page let's use the following associations: Y = Quantity X = Cost Per Item Z = Order Total
So essentially I need a formula that will automatically calculate the cost per item (X) when more Quantity (Y) is added in order to keep the order total (Z) the same.
