Cell To Generate The Sum Of The QUANTITY Of Items From Multiple Cells
Mar 21, 2014
I'd like to generate a total sum based on the quantity from each item. So columns H3 - N3 should generate a total in P3 (skipped O) by the quantity entered in each cell. In addition I'd like the value of D3 to change the pricing in the formula. D3 has a drop down with 3 products, so selecting ANY of those products should cancel out the previous formula and replace it with another.
This is the sum without a value in D3
=SUM(H3*10.00)+(I3*8.00)+(J3*6.00)+(K3*18.00)+(L3*10.00)+(M3*8.00)+(N3*8.00)
IF D3 has a package selected from the drop down, I'd like this sum to generate instead.
=SUM(H3*8.00)+(I3*6.00)+(J3*4.00)+(K3*15.00)+(L3*8.00)+(M3*6.00)+(N3*6.00)
[code]....
View 3 Replies
ADVERTISEMENT
Jul 13, 2013
I get a list of data through xml data import
column A is dates WITH time, appears like so: 2013-07-13 20:25:02
column B is the product name
column C is the quantity
now I want to search certain items sold between two dates AND times which will then give me the total amount of that item sold between those dates and times
now I know how to do this with date but have a problem doing it with dates and times
View 9 Replies
View Related
Jun 18, 2013
I have an issue when I use the sumproduct formula, it only sum the quantity and price orderly which is not matched
I have attache the file for example : Sale monthly.xlsx
View 9 Replies
View Related
Feb 22, 2014
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
View 4 Replies
View Related
Apr 22, 2006
I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price
View 2 Replies
View Related
Jul 15, 2013
How to use I have these two tables, like the picture shows.
How to get the "food" items to generate in the second table without having to enter them manually?
For example, if I were to enter 3 food items out of 10 entries, on the second table, those food items would appear.
excel.png
View 1 Replies
View Related
Nov 23, 2009
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6
Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................
View 5 Replies
View Related
Mar 11, 2009
I have a report with ~44,000 line items. From this report, I need assistance generating a report that lists all unique line items. I am having problems trying to identify which line items are duplicates as I need to compare 4 different cells in each line to other lines to determine if it is a duplicate item (compare data from columns A, B, E, F)
For example, raw data may be as follows:
Column A………Column B…….Column E…………….....Column F
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008…….22222222…….239103302000………..222.22
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008…….33333333…….353496508000………..333.33
10/13/2008…….44444444…….239103302000………..444.44
10/13/2008…….11111111…….71879W561000……….888.88
10/13/2008 ……11111111…….353496508000………..888.88............................
Note from above, there are two occurrences of a duplicate item. As a result, the output table would only list this item once.
I have attached a sample of the spreadsheet with dummy data on one tab and the desired output on another tab. Note that all the data is broken up into blocks of data that is separated by data that is only in the first column of the row.
View 5 Replies
View Related
Apr 18, 2014
I have in column A and B. A includes two (or may be more) kind of items, i.e. drink and snack. Column B has for example water next to drink and chips next to snack. The list is huge.
How can I generate a drop down list with data which uses the A column to identify the items which it should include. I want to make two separate drop down lists for drink and snack and each should include only those options which belong to those categories. I.e. drink list should be like water, cola, pepsi, etc. The problem is that the list is huge and it's not possible to sort it. Some kind of if statement structure with named range? The drop down should be dynamic in a sense that if I add line to data, it should be picked to the right drop down menu.
View 9 Replies
View Related
Apr 3, 2013
I am using Excel to generate a CSV file to load into a industrial cutting machine. The CSV file is used to import the cut list. As such formatting and the data per cell in Excel is important.
What I am presently trying to do is input the dimensions and type of the overall part and have the individual pieces be generated in a second sheet. I can presently do something of the sort using extensive 'if' statements. The problem with this is that I can only generate one line for every line I enter.
So this leads to what my problem is. I want to be able to have the info on 'Row one' 'sheet one' generate its parts list (which maybe 1-3 rows) and post that to sheet 2. Then 'row two' 'sheet one' generate it's info, but place it into the next unused row on sheet two. One of the issues I am having is that each row on sheet one may need to generate between 1-4 lines, maybe more, and I don't know how to not overwrite or have gaps on sheet 2.
Am I looking at going into macros or learning VBA?
View 1 Replies
View Related
Sep 14, 2012
way for my engineers to save a field ticket with a certain name based on data from a couple of cells in the worksheet. Re: Auto generate "Save As" filename from text and tried to use some code posted in the thread, but I an still not having any luck.
What I want to do is create an active X button when clicked on, would save the workbook to a certain folder. I want the name to look like this:
SO1!M3_SO1!M6_SO1!H2.xls
This is what I have so far:
Private Sub SaveMe()
ThisWorkbook.SaveAs Filename:="C:usersdefaultdesktop" & Range("SO1!M3").Value & Format(Range("SO1!M3").Value, "text") & ".xls"
End Sub
Would I click "general" or "workbook" in VBA when I enter this code?
View 5 Replies
View Related
Mar 27, 2014
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
View 6 Replies
View Related
Dec 26, 2013
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
View 3 Replies
View Related
Apr 20, 2014
I have two columns in a spreadsheet, and I need to write a formula to determine how many times each combination shows up. Example:
SKU-A Location1
SKU-A Location1
SKU-A Location2
SKU-B Location1
SKU-B Location7
SKU-B Location7
SKU-B Location8
SKU-B Location8
SKU-B Location8
SKU-B Location8
I want it to have the data converted to be like this (with the third column being the the number of times that combination appears):
SKU-A Location1 2
SKU-A Location2 1
SKU-B Location1 1
SKU-B Location7 2
SKU-B Location8 4
View 6 Replies
View Related
Jan 30, 2010
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".
Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
View 5 Replies
View Related
Jan 8, 2014
Basically i need a formula that will look at the message content on a seperate sheet (Data sheet on the attached example) and multiple by the volume of the message type to return a value on a seperate sheet.
The problem i have is our messages appear in the data as seperate lines based on the entire message content, with a quantity of how many times that exact message was sent on a day DESPITE there being many variations of a message, they may all only equate to 1 or 2 actual message types.
In the example you will see the Data tab showing Example message 1, Example message 1.1 etc. All of the Example message 1-1.5 are actually the same message type (same applies to Example message 2) and i need to be able to have formula that groups and counts these to return a value by message type * by the quantity per day. So in the example, Example message 1 would return a result of 10 on the main sheet.
View 5 Replies
View Related
Feb 10, 2010
Is it possible to restrict the values of cells in a particular column to entries defined in a list BUT to allow each cell in that column to display multiple items from the list (seperated by a comma for example). I've attached an example of what I would like to do -
In Sheet "2010 Data" I want to be able to select multiple values in column F....(the values are defined within the list named "Platforms" on the worksheet called "Lookups"
View 4 Replies
View Related
Nov 9, 2011
I have a list box, that I have set MultiSelect to "1 - frMultiSelectMulti"
What I would like to be able to do is select multiple items and have those items appear in a single cell on my spreadsheet.
Maybe something like: WA, CA, OR Forms.ListBox.1 is the name of my object. Or do I need to use a different object type?
View 1 Replies
View Related
Jul 18, 2014
For this ordering tool, I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the ordering sheet (we still fax orders...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.
I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.
I looked and messed around with For loops, but I can't make it work.
View 1 Replies
View Related
Jan 24, 2014
I have columns (1-7) containing values of time these columns are labelled G,R,A,S,D,B,T by 850 rows (which are locations/jobs)
The next set of columns (1-7 determins what week the work takes place) so you will get a G in a cell or GR etc.
I would like a formula to work out the sum of the time columns by the code in the corresponding cell - I am stuck!
Seantc example.xlsx
View 7 Replies
View Related
Sep 27, 2013
I'm running a pretty huge database with Part Numbers across several Suppliers which fluctuate constantly.
For example: a HEXAGONAL BOLT may have a Part Number ABC123, but depending on the size of the bolt it could be a ABC123-001 ABC123-V28 ABC123-40mm (etc)
There's a lot of data (some of it sensitive) so I don't want the users to see everything - I'm creating a front-end sheet and am looking for a way for a user to type in the Part Number "ABC123" into cell A1, then the Pivot below to auto-update (with Part Number on the 'Report Filter') to show all variations of ABC123, including ABC123-1 ABC123-2 etc - i.e. not an exact match; everything that contains the characters in cell A1.
The relevant information has been ported in and password protected on a sepearate sheet within the workbook, so it's literally a code to make the cell value affect the filter.
View 1 Replies
View Related
Jan 2, 2014
I want to sum up data for two periods (4&5) from a pivot table using GETPIVOTDATA formula. I've only been successful when I manually type the period values 4 and 5 as is shown below:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{4,5},"Project",$A7,"Category","T&M"))),0)
However, I cannot get the formula to work when I substitute cell references for the period values 4 and 5. Therefore, the following formula does not work:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{E5,E6},"Project",$A7,"Category","T&M"))),0) where E5 has 4 in the cell and E6 has 5.
View 3 Replies
View Related
Sep 13, 2009
We use a program at work to check items out to individual people but sometimes an item is missed and it takes forever to manually look for who it is. In the attached example, (keep in mind there might be more than 100 people on this list...this is just two people), the first 2 items are a pair (radio and radio case) and the second 2 items are a pair (laptop and laptop case).
Id like a way (condition formatting) to flag a person who has one item from the pair checked out and not the other. (Ive manually highlighted one person who only has one of the paired items...id like this to happen automatically)
EDIT:: If the first person were missing the radio in this example, his name would be highlighted on the line with the matching pair...cell D1 (radio case)
Other information:
Radios will always be RFAMRC***
Radio cases will always be RFAMF****
Laptops will always be RFAMTD***
Laptop cases will always be RFAMTE***
View 3 Replies
View Related
Sep 10, 2013
I have a spreadsheet that I am working on for an estimating tool for engineers in my office. I created an in cell pulldown that contains all of our pipe specs. The theory is that on another worksheet I have all the information for the labor costs, and by changing the spec in the pulldown will automatically change the numbers in the estimation table. Below is the if statement that I created. the only cell that I have the completed "if" statement in is C9, because it is adding almost double to the actual quantity that is supposed to be returned.
Code:
=IF(Sheet2!M2="SS1",VLOOKUP(A9,Sheet2!A3:J19,3),IF(Sheet2!M2="SS2",VLOOKUP(A9,Sheet2!A23:J39,3),
IF(Sheet2!M2="SS3",VLOOKUP(A9,Sheet2!A43:J59,3),IF(Sheet2!M2="CS1",VLOOKUP(A9,Sheet2!A43:J59,3),
IF(Sheet2!M2="CS2",VLOOKUP(A9,Sheet2!A43:J59,3),IF(Sheet2!M2="CS3",VLOOKUP(A9,Sheet2!A43:J59,3),
[Code] ......
View 2 Replies
View Related
May 17, 2006
I have a single worksheet containing information related to cellular charges for our company. One of the columns is titled GL Dept, and I need to separate the spreadsheet out into multiple sheets. Each sheet needs to have only the rows for each unique department so they can be distributed to each department head.
View 6 Replies
View Related
Feb 14, 2009
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products)
Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
View 9 Replies
View Related
Feb 20, 2014
I have a workbook in which users record the time spent on various activities. I have some code which will copy the worksheet template and create a worksheet for each user (in a defined list of names). Then each worksheet needs to be password protected so only the user can see it (I can do this individually by manually writing in each sheet name and setting a password but will have 80+ users for some groups and it will take ages...)
What I would like to be able to do is also automatically generate a password for each user (combining a word and automatic number e.g. "EMidsTeam123") it may be possible to insert this into the code I already have (which I have copied )...
VB:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim strPass As String
Dim lCount As Long
If Sh.CodeName <> "Sheet1" Then
'Set sLast variable to the last active sheet This is then used to return the user to the last sheet they were
[Code]....
View 6 Replies
View Related
Jun 12, 2014
I've been using excel sheets to create reports for clients based on various sized samples of bars (I'm a test engineer). The sheets are essentially the same format though information will vary (such as bar type, diameter, etc.) I've been working on automating these sheets so that I don't need so many template-like tabs (currently I have a workbook for each client setup with 5-20 different sheets just in case the client sends in those bars! Half of the sheets stay blank and it can be confusing/cluttered).
Here's what I want to do: Have one page or popup window where I can input the information (job number, bar size, bar type, etc.) press the magic button and have it spit out a new sheet with that info added into the template. Is this doable?? I've never used macros before but I'm assuming I'll need to, which is fine, how that works.
View 4 Replies
View Related
Nov 21, 2007
I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.
View 9 Replies
View Related
Jan 17, 2012
I am looking for macro where it can generate multiple workbooks with a single worksheet data source. I have this worksheet with data which is look like this:
[IMG][/IMG]
This data will be places in 1 workbook for each of line with given file name as page number above those workbooks generated will have same information for each line but at different cell position such as: take this example at line 3
and
Microsoft Excel 2007
Window 7Pro 64bit
This line could be up to 50 lines of data. I try google search but found most about consolidate multiple workbooks to single worksheet. I do have VBA reference that I refer to from [URL] ......
How to put the information from the worksheet into specific position in the workbooks.
View 1 Replies
View Related
