I have a table that has 3 columns:
Product name (one of 15 products I have)
Purchase Date (converted to excel datevalue).
Week number (a number between 1 and 52)
I'm trying to figure out how to sum the products purchased in a given week.
I've got a spreadsheet with several rows and the numbers within those rows are totalled up at the bottom. Is there a way where i can highlight the cell within the spreadsheet and it takes it off the accumulative total at the bottom? or something similar to this effect? I just don't want to have to remove figures from this spreadsheet and highlighting will be a good way of seeing what has been done
View 1 Replies View Related A B C D 1 Date code price 2 10/09/08 abc101 1100.00 3 12/10/09 abc104 1255.00 4 30/08/09 abc101 1389.00
In D1 I want the last purchased rate of a code ( suppose here it is abc101, but it would be different for other item). Here Date is dd-mm-yy.
I learnt that " " & can combine the text and figure. However, when it comes to a negative numbers, i dont know how to maneouver it. Eg, Cell A1 is USD(50,000.00) and the comment in Cell B1 is Cost is USD(50,000.00). So the function in B1 = "Cost is "& what to put here?
View 3 Replies View RelatedWould like to enter a formula in column B of Table 1 (on attached file) that would identify if that customer spent $90 or more (on any one purchaese) for any of their 4-5 purchases from Table 2. Expected results have been entered in Column B of Table 1.
View 3 Replies View RelatedI have account numbers in column A where the customers have purchased at least 1 accessory, where the accessory numbers are in column B. If they have purchased more than 1, their account number will be duplicated in column A. I would like to represent the data so that the account number is unique in column A, accessory 1 in column B, accessory 2 in column C and accessory 3 in column D.
Is there any way of doing this?
I'm open to suggestions in MS Access.
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies View RelatedI am sure this is simple but I have not found out the solution anyway....
Example:
A B C D E F G
1 2 100 0 0 2 100 400
2 2 100 10 50 0 0 700
3 2 100 0 0 2 200 600
Columns A, C and E are number of items on each "unit".
Columns B, D and F are the number of "units" (e.g. to produce).
Column G are the resulting number of items that need to be purchased.
So, column G is a sum of products and the formula found below if put in G1 will do the trick. =A1*B1+C1*D1+E1*F1. What I would like instead is to have a "formula" that allows me to insert new pairs of columns without having to change the formula to include the new column pairs.
I have a list of parts (3000+) that have different discounts.
When I enter the part#, I want the result to be"Price w/Discount.
I can do a vlookup, and also the index and match even with ctrl+shift+enter. But the results only give me just the price. If possible,
I would like for excel to do this without adding an extra column for the discount. I'm stumped on this.
Example:
In column A2:A13 I have part numbers, in column B2:B13 I have prices.
In column D2, I have the lookup and the result to be placed in column E2.
Note: I have a discount table in J2:J5 which range from
.61, .62, .72 and.79.
Basically its a invoicing system. I tried with all the products in the same excel file but then I realized if i update the product list it wont in all excel files for each customer. So i thought one external access database (or excel file) that I could refer to from an excel file.
So what I want to do now is when I want to add a new product to an invoice, I click a button (add) and it opens a small window with the list of products which has been taken from say an access database or excel file. Then I select the product and click insert.
How would I go about doing this? Are there any simple example I could follow?
i have a payroll grid. it has 3 cells with drop downs that have 39 options.
there codes for payments. i need a fourth cell to show a total dollor amount based of the codes selected.
separate question. i have a list of cells with the same four options (Job Types.ie service call, new connect...) i want to limit the cells mentioned before so that payment codes that do not pertain to the job type can not be selected.
I'm trying to write a formula to count under certain conditions. I tried the following (and some other variations), but it doesn't work, it comes back #VALUE!:
=COUNTIFS('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100,D7,'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A:$A,A34)
I need to count the number of products a rep sold in one day. He can have several sales in one day, and also sell more than one product per sale. The list I am pulling from has all sales from all reps for the day.
I'm not 100% sure how to describe what I need to do but here goes. I have two similar spread sheets. The first contains all the products in a shop with the new prices and new item codes.
The second sheet has all the old item codes old prices and some duplicates and items that do not exist any more. Both spread sheets have lots of column but only 3 that I need to worry about. Item code, Product Name and Price.
What I have tried is using the If command (in the item code column) to search through the Product Names (on the updated sheet) and put the right item code next to the right product (on the old sheet) but it didn't work.
I would like to create a new products numbers column in Excel.
I have my supplier "SKU's numbers" in column A , I would like to create my "SKU's numbers" in XXXX format (only numbers) in column B.
Could someone help me pls todo it with Macro or if it possible todo it in formula ?
The code should check in the excel sheet, that it is not generate a new SKU that was used before !
I have an Excel Sheet which has the Products column. I need to insert Category beside every products.
View 7 Replies View RelatedI have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
I am trying to identify new products that are not in my 'stocklist'. In the 'new prices' sheet I have =IF(ISNUMBER(MATCH(C4,'Stock List'!C:C,0)),"","NEW") dragged down in column G. Though its throwing out 'New' on every item which is incorrect as there are loads of items already in both sheets. This sounds confusing but if you look at the attached im sure you will see what im trying to do.
One last problem is that some of the new codes have zeros in front of them so the formula will have to discount the 1st couple of digits IF they happen to be zero. example; C5480 in stock list is exactly the same as C398 in new prices. But because there is a zero in front of the C398 then the formula does not recognise that the two are the same.
I'm trying to calculate periodically sales for new products, which have been in the market for max 6 monts. After that 6 months the sales of the product is not to be calculated. I have a huge amount of products, where this information should be calculated, so manually calculating is not an option. The products are in rows, and periods are in columns. As the data concerns several years data there is a problem, that some products have in some months zero sales, and in the next month again some sales. This messes up always my calculations. How to truly take only the first 6 months, and leave all the rest uncalculated?
I have multiple sheets in a workbook, and a want to sum the multiplication of two cells on each sheet on a total sheet. i.e. totalsheet!A1 = sheet1!A1 * sheet1!B2 + sheet2!A1* sheet2!B2....+ sheetN!A1*sheetN!B2
I need to do this for hundreds of cells, so mutiplying them on each sheet is not an option. There are enough sheets that writing out the brute force equation as above is too long. I know you can do total!A1=sum(sheet1..sheetN!A1) to add all sheet A1's together, but multiply and sum?
What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).
View 6 Replies View RelatedI am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
Here is what i have so far: stock.xlsx
Basically, i have 2x base products but we can interchange the parts between the sets so we have 1 variant per base product.
But what i want to calculate is how many sets i can make out of the available units that are in stock.
So I have a spreadsheet of several thousand products (spare parts) and we received a folder of image files to go with these parts, however the file names aren't all uniform; some of the image files have the Part Number, while others use the SKU number. I have cells with the part # and SKU number for each product, and I could probably copy and paste the list of all image file names from the folder into the same spreadsheet. Is there a macro that could search the image file list for either the SKU or Part Number, and whichever is listed for each row, insert that file name into a cell? So I have this:
Item name SKU # Part #
Spare Part 1 123456 P0459381B
Spare Part 2 340934 P2394093A
etc
And then I have the list of image files in its own Sheet or whatever's easiest
P0459381B.jpg
340934.jpg
etc
So after the macro is complete, I'd have:
Item name SKU # Part # Image file
Spare Part 1 123456 P0459381B P0459381B.jpg
Spare Part 2 340934 P2394093A 340934.jpg
Try this on for size...
On the MSP sheet is a list of products with a weeknumber and quantity.
I need to combine products that match in the same week and add their quantities.
Then I need to put the quantities into the VOLUME worksheet.
Any products that arent listed need to be added on the bottom.