I am trying to graph data from an Array of sheets. I keep getting many errors... I tried using Cases too but this failed, it graphed the first Sheet but nothing else.....
Before Array Works! (Uses Cases)
Code:
Sub GraphTest4()
Dim LastRowGraph1 As Long
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
[Code] .........
I'm trying to summarize some data across 12 sheets
I'm trying to look up values against dates and specific items, however they repeat across different sheets.
The attachment has more detail : Lookup x across multiple sheets bring back value.xlsx‎
I am attempting to graph data based on work down in specific work centers to track efficiency. I can download all items that were finished for a certain time frame (yesterday, last month, last year, etc.), but the data only shows up as the item, quantity, and the date of closure. I take that info and copy and paste it to the close out tab of the other work book, where it references another sheet to pull in the time required to complete the task, and the work center that did the work. My problem is when an item has multiple work centers on the same item and then taking that info into a pivot table to graph. This can be done manually, but I was hoping I could create a way to only have to copy and paste the data and have it return the work done.
I have included a shortened version of what I am trying to do, eventually there will be 25000+ items on the data tab, but if I can get this to work, then i assume I can extrapolate.
I am currently using the array formula to find out the most recent date a business as referred a deal. I have been using the below array formula to figure this out...
{=MAX(IF((Opps!A:A=[@Company])*(Opps!B:B=[@[Full Name]]),Opps!G:G,0))}
However, we have now have two types of deals. Opps and Suspects. SO now i need to figure out the most recent date from Opps!G:G and Suspects!G:G... regardless of what the deal type is. Hope that makes sense.
Basically i need to have a formula along the same lines as above but MAX date from if conditions from Opps OR conditions from Suspects
I have a list of cell entries that I want to find and replace with different text or a number.
My code below is 4 of them.
I am trying to do the replace over 3 different sheets at the same time but I am only changing the 1st sheet with my efforts.
Sub Find_And_Replace()
Sheets( Array("Resolution", "Response", "Open")).Select
Sheets("Resolution").Activate
Cells.Replace What:="1 Widespread*", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="2 Critical*", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="3 Non*", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(Array("Resolution", "Response", "Open")).Select
Cells.Replace What:="4 Require*", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
I have the theoretical set:
year $ %
2003 100 10
2004 120 20
2005 145 30
2006 205 40
2007 300 50
Could I make the year the X-axis and make 2 y-axes on the same graph in excel that each datasets would be correlated to (eg. for money it'd be from 0 to 300 while for percent it'd be from 0 to 50)
I am trying to use the SUM IF Array formula to sum a group of numbers that fall under a heading of reference numbers over several sheets of data. For example purposes lets say my spreadsheet looks something like the below.
A
B
C
D
E
1
2600000248391
2600000393805
[code]......
The first two digits of the heading numbers are the criteria I am trying to use to separate and sum the data. For example I need to sum the value of the data below headings that falls between 1400000000000 and 1499999999999.
For the example above I used the below formula for the current Sheet and it works fine.
{=SUM(IF(A1:E1>="1400000000000",IF(A1:E1="1400000000000",IF(Sheet1!A1:E1,Sheet2!A1:E1
I have a spread sheet that we input data on and produce a simple graph. When I started we plotted these graphs by hand on chart paper. When we started using spread sheets I learned how to graph the problem is these guys we have working in the field don't know how or care to learn how to make a simple xy graph.
Six months ago I started automating a workbook so our field guys could just plug in numbers and have the needed graph, I put check boxes to select the points to calculate the slope and intercept. I hard coded to plot to say line 21 then at the end of the test we always have a zero point that we don't plot so I put that on line 22 then have a macro to hide everything before I print it.
I think I would like to dynamically select the data for graphing but I'm not sure about the best way to do this. My first thought is to key on the x axis column with a loop and graph data from zero until the x looses value.
I have researched and read multiple threads related to graphing rolling averages per month. I've setup my own but I cannot get it to graph correctly. The x axis will be the days of the month ie. 1-31. The y axis is obviously the sales volume. The legend should be a line graph for each individual month sales. I want the graph to reflect the current month and previous 12 full months.
I used the file user rockycj started for another user in a different thread labeled, "Excel Line Chart - 12 month chart moving with dates ". It was very helpful and I'm trying to get this completed before my deadline. If anybody could take a look and let me know, I would greatly appreciate the help. I have attached the file for reference.
I have got a table of results with wards along the columns and in the rows reading left to right weekly dates e.g. 5/10/08 12/10/08 19/10/08 etc.
How can I get a graph to change the data it displays from a combo box eg. A1 is selected in the combo box and it shows a line graph of the Data for A1 over the weeks? There are 50 wards
How do I graph this data? I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.
I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.
Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered
NO
Employee Name
Question
1
2
3
[Code]...
How can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
I've attached a sample sheet to this message.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
Sample Data.xlsx‎
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I have workbook with 12 worksheets (sample show only Jan and Feb sheets) from which I want to summary data onto a single Summary (see tab on worksheet) sheet. Each worksheet has three sections: Income, Expense, and Spending. From each of these sections I want to summarize data from columns: Description, Amount and Date.
For example, from Income section of all worksheets, I want Excel to look at the Descriptions column and total the Amounts for all items with the same description, that fall between two dates. Then Excel look at Expense section Description column and total the Amounts for all items with the same description, that fall between two dates. Then do the same for the Spending section.
The descriptions who's Amounts to be totaled cannot pre-exist on the summary sheet, so I need Excel to fine each unique description, list it once on summary sheet, then calculate the total. I’ve attached a sample file. The SUMMAY EXAMPLE sheet is what I would like the result to look like on SUMMARY sheet, but I’m open to a different layout.
I have an excel sheet with multiple tabs (Master, Sheet2, Sheet3, etc.). The Master sheet has a list of all current employee names in Column A and I want to add a Button to the sheet that will produce a message box to add a new name to the end of this list and after the last row of data on each subsequent tab (Sheet2, Sheet3, etc.). The problem I'm encountering is that the code I have requires me to select a cell and is just inserting a new row with the data on each sheet at the same point, so if I have row 14 selected the new entry is created at row 14 on each tab...
View 8 Replies View RelatedI have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.
Column 1 Lists the various factories
Column 2 Lists the Machine
Column 3 Lists the Part
Column 4 Lists the result.
Results can either be "ok", "warning", "Alert" only
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
I'm working on a sheet trying to keep track on which client is working with which employee. The first sheet shows tracking for the year and each additional sheet is the month. Basically the Yearly sheet is there so I can quickly control+f "client name" and see which employee he or she is working with.
I attached a simple version of the workbook but basically it basically looks like this: Yearly sheet - Row 1 is the employees name, Row 2 is the month, and the rows after that are the clients name until it gets to the next month. January sheet - cell A1 says client, Cell B1 says employee. I'm inputing the clients in column A and the employee in column B and using this formula for the Yearly sheet (starting in row 3) =IF(January!$B2=Yearly!A$1,January!$A2,"")
This is working for what it's supposed to do, but it's not pretty and leaves a lot of of blank cells. Is there a simpler way of doing this?
YearlyClientsEmployees.xlsx
I have a to do list on excel which spans 5 separate sheets (all within one file). I have given each item a priorty number e.g. 1, 2 or 3. I wanted to summarise on the first sheet how many priority 1s, 2s, 3s I have.
View 2 Replies View RelatedI want to consolidate the various employees' salaries of all months in a sheet. I enter salaries in different sheets month-wise and in each sheet, department-wise. Some employees get commission in various departments. Now, I need to see the details of an employee by giving his name. I should get month-wise his salary, commission and department in which he get commission, across all the sheets.
View 1 Replies View RelatedI am using the following code to copy data from multiple tabs to a summary tab - it is only supposed to copy the data in the rows if the cells in Column A have data in them, its working, but for some reason it is copying the data in columns Y and Z for four extra rows even though there is no data in column A for those rows.
View 5 Replies View RelatedI have created this with text, command & list boxes with a view to creating an asset register.
So the options i have are premises, hardware, software & fixtures. I have tested so that these go onto 1 sheet ( at the moment all additions go to premises) but i require for it to go to there respective sheets i.e. a sheet for each asset.
But, due to inexperience with userforms and VB i don't know how to do this.
Also, how can i get a sequential number coming up for each asset on their respective sheets
I have the following (same fields) data in multiple sheets (named P1, P2 etc).
Would like to get the result as a table, based on the sheet name and the cell reference in that sheet.
P1 (Sheet Name)
row A B C
1
2
3
4 Data1 x
5 Data2 y
6 Data3 z
P2 (Sheet Name)
row A B C
1
2
3
4 Data1 a
5 Data2 b
6 Data3 c
New Sheet (Intended format of the result, based on the A3, A4 and B1, B2, B3 values)
row A B C D
1 Field Position B4 B5 B6
2 Sheet Name Data1 Data2 Data3
3 P1 x y z
4 P2 a b c
Im looking for a way to copy all rows with data in them from row 3 to the last row with data to another worksheet. I would like to copy the data from ALL worksheets in the workbook apart from one called Grade Boundaries.
All the sheets have the same layout. I simply want to produce a sheet with the data from all sheets in one place.
I have a excel doc we use at work to create a schedule for our technicians.
Each sheet is a week, so the sheet names are Week 2, Week 3, Week 4.
Each technician number is listed in column B (3,4,5,36,53,91, etc)
Row 5 has Mon-Sun
The techs work schedule is in the appropriate cell. (8 to 5, 10 to 7, Vacation, etc)
I want to count the number of shifts for each tech each week, with a running total for the year. It was easy to do it on each sheet with a simple countif formula. But The problem comes when I want to count them for the whole year.
I tried creating a "stats" sheet and make vlookup formulas to call the data from each weekly sheet. But with all the techs and shift types I want to count, it was like 40,000 cells. Excel wasn't able to calculate it, it had the "processing 0%" in the taskbar.
I thought I could use =sum(Week1:Week52!AZ6:BN50), which is where I have the counts from each weekly sheet. But my data is not always in the same spot on the sheet. Because of techs coming and going (new hires, people quit).
there's a way to pivot data from two sheets (both the sheets and the pivot table are in the same workbook)?
I have multiple sheets within a workbook, where the sheetnames will always be changing.
Inside of these worksheets there is data that will be different, the starting cell of the range is allways the same and the number of columns is constant. The number of rows changes. I need a code that will go to each worksheet, define and copy the range on that sheet and paste it onto a summary sheet, in order.
I would like to collect all the data and put it onto one sheet.
I am trying to populate a TreeView control from multiple sheets. I have managed to populate it from one sheet but can't do it for multiple sheets.
see my code below:
Dim arrName As Variant
Dim arrParent As Variant
With Sheets("Sheet1"). Range(Sheets("Sheet1").[A2], Sheets("Sheet1").[A65536].End(xlUp))
arrName = .Value
arrParent = .Offset(, 1).Value
End With
I want to be able to make it look up from Sheet2 aswell.