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 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?
I 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
I have a 5 major categories of products e.g. Ketchup, Hot sauce ,mayonnaise , pizza sauce etc.
Now each of these individual categories are further divided into number of of subcategories based upon the the sizes and brands.
I want to create a sheet (One sheet) where I have to record production of each product on daily basis.
Like for example if I am producing ketchup, I select ketchup from drop-down list,Now in next column I have to select production size from predefined sizes i.e subcategories. But it should also be from drop-down menu and it should only show me the subcategories of my selected product like ketchup .I think this has something to do with data validation but I am not sure how this model will work.
I've got data in two columns (J and G). Column J contains product manufactured by companies in column G. Some products in column J are manufactured by more than one company in column G. I'm trying to create a conditional format to highlight products made by multiple companies.
My thought is that I need a formula that asks if the product is duplicated, AND the company is different, but I'm not sure how to do that.
I 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.
My userform requires a user to enter amounts in 5 different textboxes.(textbox1-5) I have created a textbox6 to attempt to capture the totals (should be numerical) of textboxes1-5, even if this textbox figure is a 0 or a minus figure. I have browsed a few other posts with roughly the same issue and have come up with some basic code as per below... the code is pasted into each (textbox 1-5) textbox_change() code.
If TextBox1.Value = "" Then Exit Sub
If TextBox2.Value = "" Then Exit Sub
If TextBox3.Value = "" Then Exit Sub
If TextBox4.Value = "" Then Exit Sub
If TextBox5.Value = "" Then Exit Sub
TextBox6.Value = CDbl(TextBox1.Value) + CDbl(TextBox2.Value) + CDbl(TextBox3.Value) + CDbl(TextBox4.Value) + CDbl(TextBox5.Value)
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'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 RelatedA$ SING $ Euro NOK UK US$
1.07001.25000.74186.10000.62081.0000
A 934.58
B 800.00
C 1,348.07
D 163.93
E 1,610.82
F 1,000.00
G 1,869.16
H 1,600.00
I 2,696.14
J 327.87
K 3,221.65
L 2,000.00
TOTAL 17,572.23
Values in column "H" (US$) have been divided by currency rates in Raw C2~G2 as we need the total in US $
But we also want to know the total amount of each currency used in column "H".
How to put a formula to find the total value of each currency used.
see attached file.
I am looking for a macro that will look at the data and for each Item Description (Column C) I want it to sum the values in Column J and put in cells M1:Nxx. See example.
i tried using the autofilter and creating tables, but makes the sheet large in size...
I have one workbook in that 6 sheets have.
Here I want match the total one sheet to another but each sheet there is no fixed ranges so that I have confused.
How to find out Each sheet total and match each other.
below the following lists (Name, Week, Amount) I am trying to calculate a sum of the Amounts column (Column D) for two conditions: a particular week # (Column C), for a particular color (Column A).
Is there a simple formula to SUM numbers when it is based upon two conditions (color and week #)? For example, there are two separate RED amounts that should be received in Week 4 - how do I write a formula for the cell that correlates RED and WEEK 4 that states "sum the amounts for the Name: Red and Week: Week 4"?
NameWeekAmount
GreenWeek 1 5,000
RedWeek 4 13,000
BlueWeek 1 1,500
GreenWeek 3 3,500
GreenWeek 2 4,200
PurpleWeek 5 1,200
YellowWeek 4 500
GreenWeek 3 25,000
RedWeek 4 13,000
RedWeek 3 12,500
RedWeek 1 10,000
BlueWeek 5 9,000
BlueWeek 3 14,500
YellowWeek 5 3,300
GreenWeek 2 1,000
YellowWeek 4 30,000
Week 1Week 2Week 3Week 4Week 5
Red
Yellow
Green
Blue
Purple
TOTAL
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 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'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 ~500 rows of data in columns A, B, C that is as follows, for example:.............
I am having trouble with coming up with a formula that will add up the total items shipped for each item. For example, Apples = 62 items shipped.
I have a workbook containing 120 sheets. Each sheet contains a column labelled "Subject", and a row below labelled "Totals:" with a numeric value in the intersecting cell.
I need a formula that will total the value in all these cells on the last sheet.
The trouble is, the cell address of the intesecting cell fluctuates somewhat from sheet to sheet because the column and row for the "Subject" and "Totals:" are not always the same.
******** ******************** ************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF30=
ABCDEFGHIJKLM5**Check*Date*01/05/2007*to*09/28/2007,*Job*616003*to************6**6440009,...,Job#*642010*-*HEATHER*GLEN@MONUMENT*BO************7*************8***Record#****Check#**Period***Employee*****9****Comp*Code*********Hours**10*************11*************12*************13*Totals*by*Comp*Code:*************14*Comp*Code*************15*****Hours****Wages***Overtime**Subject***Rate*16*************17*5183***PLUMBERS*UNDER*$23*************18*****19.00****376.50****376.50***11.5400*19*5187***PLUMBERS*ABOVE*$23*************20*****5.00****120.00****120.00***6.5300*21*************22**Totals:***24.00**496.50*0.00496.50**Sheet8*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I want to be able to total the "Subject" wages for codes 5183 & 5187 (unfortunately, the payroll amounts are located one row below) for all 120 sheets in the workbook.
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 RelatedHere 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.
I want to produce a spreadsheet which automatically tidies up a transferral of data from a programme called sage in to a customer service spreadsheet for my company.
I am a bit of a novice but a keen one so please be patient! I do know what I want to achieve in the long run!
The problem I have at the moment is recognising products in one column and getting it to return the value in or out of stock in another. So far I have got it to recognise one product and return the correct statement (using the "IF" function) but i need it to do it for the whole column and for a range of products.
I am trying to write a VBA code that does the following: The table below provides a sample of the Source (i.e how we start off with) and the Result (i.e how the format needs to look like). More specifically, for each product in 1st column that has a number in blue and or red column i need a row inserted immediately below (hence the counta function to indicate how many empty rows to insert). The end result should have a separate line for each blue/red colour depending if there is a number shown in the source data. Obviously the result should be displayed in another sheet (i.e sheet2).
PRODUCT
BLUE
RED
COUNTA
PROD A
9902
1
[Code] .......
i have been trying to find in C1 the 1st product in a list of products that is in text strings of various lengths and the product will be in different places each time eg; list in A1:A4 is babydoll camisole corset basque, the text string in B1 is Satin strech babydoll with low neck
in B2 the text string is Beautiful lace basque with corset style back.
in C1 i'm looking to find babydoll from B1 in the list A1:A4
in C2 i'm looking to find basque from B2 in the list A1:A4
in D1 i'm looking to find any 2nd match from B1 in the list A1:A4, result = ""
in D2 i'm looking to find any 2nd match from B2 in the list A1:A4, result = corset
in E1 & E2 i would like to find any 3rd matches
i have been reading up on this and trying formulas but i cant figure out how to find from a list and the text string data is not in a standard order.
I'm creating a spreadsheet that has:
- budgeted dollar amount
- entry each week for that week's total expense
- total expense to date
Question: how can I hold the value for "total expense to date" so that when the new week's info is added the previous total is not lost?