I have an Excel spread sheet as shown below. Sometimes, there are two rows (could be more )with the same items-for example, such as Inventories, but different values. How can I sum those rows with the same items and put it in a different column, and in the same time also copy over the single item to the same column? ....
View 9 RepliesI have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
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.
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.
If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..
View 4 Replies View RelatedI know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
1) How do I get the three (3) items columns to be right underneath the ''Recovery Turnaround Days of Files Per Cheque'' heading
2) How do I get each of items #1 - 3 column, to have each their own color (that is not the same color as the '' Average Recovery Days '' column color) ie;
Item #1, 30 = I would like it to be say, Red
Item #2, 35 = I would like it to be say, Pink
Item #3, 30 = I would like it to be say, Yellow
3) How do I get the column under the ''Average Recovery Days'' heading to have it's own color scheme as well, such as as say the color ''Purple'' (or any other color that is different from all the other 3 columns).
how you go about putting the columns so they align right underneath the particular heading, that would be awesome. Also, how to have a color scheme for the ''Average Recovery Days'' to have it's own color (For the column under this heading to have it's own color, what ever color that is), that does not match any of the column colors in items #1 - 3 (under the heading ''Recovery Turnaround Days of Files Per Cheque''),?
See attached sample file. Any way to get column graphs next to each item (horizontally), each column being the revenue for that item.
Attached File : Sample.xlsx
I need to add items to an array and used code out of the web to create some kind of an array list.
View 12 Replies View RelatedThe below code shows the way I add items to 10 different comboboxes. However, the items to be added are actual the same.
Is there a way I can simiply it? I am worry that by the end of day, i might have up to 50-60 comboboxes and the code will be a relativity long and not efficient.
Taking into consideration the the comboboxes will be labelled as Jieyi_task_ComboBox1...to 10
jane_task_ComboBox1...to 10
Kenny_task_ComboBox1.. to 10
getting correct formula and normally there are people much smarter then I in the
discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1)
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere
3 Mr. Someone 327 anywhere
Drver numbers are blank
On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers
numbers already filled in) I have this set up as a list.
A B C
1 Name Origin Driver #
2 Mrs Soandso 125 anywhere 37
3 Mr. Someone 327 anywhere 39
What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it.
I have a monthly calendar on each sheet for employees, attached is a copy of one of the sheets. I can have up to 2 combinations of text in a cell, i.e., 'V' or 'T' in this example, followed by a number, that can range from 1-12, i.e., V12T4. What I am trying to do is add up the total 'T' and 'V' time for the month for each employee. Hopefully between what I have written and my attached sheet, will explain what I am trying to do. I know how to write the =Sum(If for using only 1 item in a cell, but not 2.
View 14 Replies View RelatedI am working on a set of data and need to group items in the same style together.
Example: ...
I am setting an array with
Code:
Set my_array = Sheets(1).ListObjects(1).DataBodyRange
Column number 2 contains numbers, but I want to 'reset' all the numbers in the second column. How can I do that? I know I can do a loop through all the elements, but I thought it would be smarter if I could set the entire column as zero in the array?
I have a code which works perfectly and adds the dates from Column A to UserForm1 ComboBox2.
In column B, I have textdata - UserForm1 ComboBox5.
I'd like to transform the code to add only the dates which correspond (cascading) to 1.1 textdata in ComboBox5 (Column B) and 1.2 dates in ComboBox2 (Column A).
Code: ...
I have a pivot table with a calculated item. The problem is that it shows data for all rows EVEN when there is no data there. So the pivot table is a lot longer that it needs to be. How do I get the pivot table to show only rows with data in it? Also, all of the fields DO NOT have checks beside "Show Items With No Data".
View 9 Replies View RelatedCould the code be modified that if BSPT were selected only certain thread sizes would be shown in the combobox as the sizes below.
1/16"
1/8"
1/4"
3/8"
1/2"
3/4"
1"
1 1/4"
1 1/2"
2"
2 1/2"
3"
4"
5"
6"
If NPT were selected have only the thread pitches to be shown in the Combobox below
27
18
14
11.5
8
can be selected
If BSP were selected different thread pitches to be shown in the Combobox below.
28
19
14
11
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "SELECT THREAD TYPE"
.AddItem "NPT"
.AddItem "NPTF"
.AddItem "BSPT"
.AddItem "NPS"
.AddItem "BSP"
End With
I have print macros that print several different items from more than one workbook all at once. It works fine when printing to paper - you go to the printer and pick it all up in order. The problem is when I try to print it to a PDF - it prints each item to a separate file. The code below doesn't work - the second item printed just replaces the first item printed.
Sub PrintToPDF()
Dim strNewFileNameHere As String
strNewFileNameHere = "SomeFileName" & ".pdf"
ThisWorkbook. Sheets("Sheet1").PrintOut ActivePrinter:="Acrobat PDFWriter on LPT1:", PrintToFile:=True, PrToFileName:=strNewFileNameHere
ThisWorkbook.Sheets("Sheet2").PrintOut ActivePrinter:="Acrobat PDFWriter on LPT1:", PrintToFile:=True, PrToFileName:=strNewFileNameHere
End Sub
How can I call several different print macros and get them all to print to the same pdf?
I want to add colour background to every other line in a Listbox (like the old computer paper green/white lines) but we know this can not be done, so everyone who knows says use Listview instead. Try as I might I can not seem to add items to the Listview control though.
This has got be one of the dumbest questions, but how the heck do you add items, its so easy with Listbox.
As an aside I wonder why no bright spark has not written a control that replaces Listbox with extra features yet.
I am using this code:
VB:
Sub a()
Dim WB1 As Workbook, WB2 As Workbook, MyFile As String, Sep As String, WS As Worksheet
Dim R As Range, X As Integer, a As Range, b As Integer, rownum As Integer, WS1 As Worksheet
Dim R2 As Range, a2 As Range, row As Integer, sep2 As String, MyFile2 As String
[Code] ....
If I run this code without the miles of code i have between them, it loops through each workbook in the folder just fine.
However, I am getting a Runtime 5 error when it reaches Myfile = Dir() each time with my full code.
Here is the full code:
VB:
Private Sub CommandButton1_Click()
Dim WB1 As Workbook, WB2 As Workbook, MyFile As String, Sep As String, WS As Worksheet
Dim R As Range, X As Integer, a As Range, b As Integer, rownum As Integer, WS1 As Worksheet
Dim R2 As Range, a2 As Range, row As Integer, sep2 As String, MyFile2 As String
Sep = Application.PathSeparator
[Code] .....
Once again, I am getting the error message 3 lines from the very bottom ("MyFile = DIR()")
Ok, so basically I've been asked to create a chart that lists the top 3 types based on count per year. I don't want to make 4 charts, but my top 3 types differ by year.
Type would be column #1, and Count would be column #2.
Type Count
1 7
10 6
11 7
12 5
13 24
14 2
15 7
[Code] .....
I have two items: Written Premium and Earned Premium. Each of these items has a forecast and actual number for two months. How can I create a line chart (with dots) that will show both of these items. I've attached a spreadsheet.
View 1 Replies View RelatedI 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.
I need a separate wb
shows style and pluses all quantities
style quantity
or12 6
or34 5
or45 6
I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.
Spreadsheet 1: Search by Number
Column A is a list of names
Column B is a list of numbers
Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.
Spreadsheet 2: Search by Name
Column A is a list of names
Column B is a list of offices
Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.
I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.
I have a list of patients with different categories. Count values of HBA1c which are less than 6.5 and those greater than 6.5 ONLY and only for those of the Category named "PreGDM" but not for other categories.
View 14 Replies View RelatedI been working on excel sheet that will distribute 200 items between the employees that are available to work. I've attached a copy of the workbook. First I need to look at the quantity of employees I have working that day. Second Distribute the items to each one of the employees. Third give the first employee the first piece. Fourth give the next available piece to the last person with a zero piece in their list.
book8.zip
I am trying to find the min value in one column for each values in another.
Length Name Min for Name
1 Dave Formula??
2 Joe Formula??
3 Fred Formula??
3 Dave Formula??
3 Fred Formula??
3 Dave Formula??
5 Joe Formula??
I am making a rough gantt chart in excel for a project schedule. Japanese at work love excel fro some reason that is why I am trying to do this. I have column headings for days ( 3/3, 3/4, 3/5, etc) and I want to find a way to mark the intersection when a date is input in another row. I tried to do some conditional formatting using symbols, but couldn't get anything to work.
see picture. I want to mark at correct date according to column headings when date is placed in column E or F. This example shows column E with 3/10 so I want to make a value appear at intersection AS and the row 3/10 is on. Would like to get a symbol to appear there if possible.
excel help.png
In sheet one, down column A, I have a list of numbers 1-100. What I want to do is have those numbers be removed from sheet 1 when entered in column A in sheet two. For example, in sheet 2, A1, number 53 is entered. I would like number 53 removed on sheet 1.
View 9 Replies View RelatedI have a workbook (attached).
I want to count the number of times North AND Win, North AND Lose etc.
Can anyone provide me a way of doing this. I dont want to have to use B2&C2 for example. I know I could do this and then use an IF statement (I think).