I have a spreadsheet, whereby I have a list of items that grows regularly, but also have a brief summary of items, however I would like the summary to float, so that it always appears on screen, and I don't have to hunt for it on the spreadsheet.
View 2 Replies
excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.
View 3 Replies View RelatedI have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
I have a page of data that i need to summarise/calculate, i thought sumif would be the correct formulae but i can't get it to work...
Sheet 1 - Data Recomds Emp Name, Weeks 1-52 showing no of hours to adj
Name
WK1
WK2
Wk3
WK4
WK5
WK6
Oliver
-1.5
[Code] .....
Sheet 2 - Summary by month - to Calculate the no of hours for the period per employee
Name
Month 1
Avery
Require Sum for employee Avery Wk 1-4
[Code] ..........
I work in a medical facility, and I need to track data I've entered representing patient visits indefinitely over time. I've attached a weekly grid to this thread that I use to record which patients have had visits during the course of the week. It sums their total visits on the right. I need to continually track these because every patient gets an insurance-mandated progress update after 10 visits. Unfortunately, they don't always show up, and the most I can possibly keep track of is two weeks, max, and most patients only get two visits per week. Is there a way to dump these sums into another column indefinitely?
View 1 Replies View RelatedI have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
View 14 Replies View RelatedFor instance, in travels.xls a table in the 'data' sheet lists destinations versus people and the data of travel.
What i'd like is to assign a macro that would have a breakdown of the number of travels per destination that month. Where there is no travel that month, that destination is omitted.
I can work out how to use SUMPRODUCT to produce the number of trips per destination within that month, but stuck as to how to get it to display it using a macro.
Should I use a loop to loop through all destinations, copy that information to the separate sheet and then another loop to delete destinations with zero trips?
eg.: productlist + order amount + sum
Sheet: Masterdata
Column A: Productname
Column B: Product price
Column C: Product quantity
Column D: Total (=B*C 'obviously)
Sheet: Summary = All rows of Masterdata WHERE Product quantity Is Not Null (<>0) starting from row 5 in the summary sheet. note that if a value in quantity has been reset in the Masterdata, the Summary has to be updated. I do not want to use an advanced filter because the whole workbook needs to act like a portable template for various users which once in a while gets updated using external connection with a ms access database. ( Import of access report). I have no idea whether a copy function or customized pivottable (does not works because too much data), ... would do the trick.
I have a very large table that I want to be able to summarize neatly and columns that have zeros for a specific person, so its easier to input data later.
View 1 Replies View RelatedI have 9 sheets of sales data, some with over 50,000 records. I need a way to present this information in another worksheet so I can bring up just the data based on individual brands. I understand a pivot table is needed however I cannot see how to gather data from multiple worksheets.
For example:
I need to look at all the different sales for Product A, which appears in multiple worksheets. Product A is broken down to store-level, with records for number of sales in each store, one record for units and one for value (for the same store and product). Is there a way I can get the data for all of Product A in one worksheet so I can then easily add the totals across all stores by both units and value?
E.g.
Store 1 - Product A - 10 units
Store 1 - Product A - £20
Store 2 - Product A - 7 units
Store 2 - Product A - £14
A) Is this even possible to do in Excel? I've seen similar ideas in foums, Excel help, etc., but they are all limited to worksheets within a single workbook.
B) My front half logic will work if I can do the back half. But if that's not possible, is there another way to accomplish this?
I am looking to create an excel sheet with Staff in different positions who work on a rota basis. So for Example I will have something like below:-
Week 1Week 2Week 3Weeks 4Week 5Team LeadersFredYesNoNoYesYesHarryYesYesNoNoYesBobNoNoYesYesNoKarenNoYesYesNoNoMechanical TechsDavidYesYesNoNoNoGlennNoYesYesYesNoTonyNoNoYesYesYesProduction TechsEddieNoNoNoYesYesChrisNoNoYesYesNoMikeNoYesYesNoNoCharlesYesYesNoNoNo
What I want to do is create a summary sheet which looks like an organisational chart with boxes and lines of hiarachy, and When I choose Week 1 it selects the Team Leader(s) who are working in week 1 and puts them in the correct box in the org chart, then the Mechanical techs and puts them in a different box etc. So when I change the week number and want to know who will be in post in week 5 for example it puts the correct names in the correct boxes.
I have a workbook in which a userform pops up which I use as an assessment form on my fitters. It goes through a series of questions on then when the submit button is clicked a sheet is added to the workbook which is named with the fitter's name and the current date - all this works fine...... what I now need to do is to create a summary page which will include basic information from each sheet in the workbook, bearing in mind that new sheets are constantly being added to the workbook.
View 9 Replies View RelatedI have the following table for results in 7 races by 8 competitors (NB Not all competitors compete in every race)
Competitor Race 1 Race 2 Race 3 Race 4 Race 5 Race 6 Race 7
........A...........3..........5..........4.........6..........4........1
........B...........2..........4..........3.........4..........6........3
........C...........4..........3..........2.........5..........5........4
........D.......................6..........1....................7.........2........3
........E...........7..........7..........8....................3.........5........4
........F...........1..........2..........6.........1..........8........8........1
........G...........5..........1..........5.........2..........1........7.......5
........H...........6..........8..........7.........3..........2........6........2
What I'd like to have is a table with summary results by the follwing criteria:
Most 1sts:
Most top 3:
Most bottom 2:
Lowest average position:
Highest average position:
What formulas would I have to use to get each of the 5 results?
1. Count of Unique Print Customers
2.Total of Print Revenue
3.Count of Unique Online Customers
4.Total of Online Revenue
5.Count of Customers advertising in both Print and Online
6.Total Online and Print Revenue
I have a report of 1200 plans (approx 2000 rows). Attached is just a small example of data that comes from the report. There are 3 plans in column B (Plan X, Y, Z). I want one row of data for each plan with the plan roles and Employee ID filling those roles listed horizontally instead of vertically. It is only the roles and Employee IDs filling the roles that cause the data to be more than one row. In this example, I’d want only 3 rows of data (one for each plan). I want to include all data shown for each plan but want it on one row per plan. Possible roles are Plan Owner, Plan Steward (there can be up to 10 stewards per plan), Plan SPOC, BU SPOC, Compliance Director, and Officer.
View 5 Replies View RelatedI have formed data arrays in VBA after running a time series simulation model. The array is m simulations x n periods. Small example: ...
View 6 Replies View RelatedI have many checkboxes (1 to 26) that I want visible or not, based on whether the cell states "n/a" (are in a column in a different worksheet). Rather than writing this code multiple times, I'm looking to condense the code.
[Code] .....
I have data like this:
A B C
1 INC0123 00:00 00:12
2 INC0123 00:13 00:27
3 INC0123 00:28 00:42
4 INC0127 00:00 00:20
5 INC0127 00:21 00:48
6 INC0128 00:00 00:22
etc
and what I would like is a summary sheet that looks like this
A B C
1 INC0123 00:00 00:42
2 INC0127 00:00 00:48
3 INC0128 00:00 00:22
etc
summarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.
View 4 Replies View RelatedI have a summary tab in which I am trying to sum data based on specific variables from 30 other tabs in the worksheet
- In the summary tab, I have months (one year worth) over the top row and consultant names in the first column
- Each other tab represents a project
- I want for consultant 1 in summary tab, to have:
For january, the sum of dollars spent in january in each project (so across all tabs)
Same for february to december.
I have attached an exemple Note that the consultant names will always have the same syntax but will not necessarily be in the same cell in each tab.
I have daily Open, High, Low, Close stock prices. Separately, I need the same data but in weekly format, so:
1. The weekly open is the open of the first working day.
2. The weekly high is the highest of each of the daily highs.
3. The weekly low is the lowest of each of the daily lows.
4. The weekly close is the close of the last working day.
How can I do this using formulas?
OHLC sample.xlsx
I've a table and in the first column titled 'VALUES' (A1) I've entered values ranging from 1 to 100 (A2:A10). In the second column titled 'STATUS' (B1), 'closed' or 'NA' is entered (B2:B10). Now I need to find how many cells are there in the table with 'closed' status in the range 0-25, 26-50, 51-75 and 76-100.
VALUES STATUS
34 Closed
56 NA
44 Closed
98 Closed
18 NA
82 NA
23 Closed
40 NA
63 Closed
71 Closed
Closed Status Count
0 - 25 :
26 - 50 :
51 - 75 :
76 - 100 :
I think VLookup is what I need to use, but am unfamiliar with how to use it. The attached file will explain a little more about what I would like to do. I have an inventory summary from 2007 and 2008. Each year has its own sheet. Each record has two fields that need to match on the summary sheet. If the two fields match, the summary sheet should return a total in the third field. It's more clearly explained in the file itself.
View 2 Replies View RelatedI'm looking to create a summary sheet of all data consolidated to one tab.
On the attached workbook, I want to pull all of the data from the monthly tabs and consolidate it all to the 2012 Totals tab.
How to only pull the nonblank rows, and ignore the headers.
I would like to be able to avoid using a macro for this, if at all possible. I would like for the summary to update dynamically whenever new data is added to any of the monthly tabs.
Link to spreadsheet
I presently have a workbook that contains a summary sheet called "Sum" and a few other worksheets called Building A1, Building A2 and so on until Building A5. All the worksheets have similar layout with the Header at Cell A4 to H4 and the data starting at Cell A6:H6 onwards. At present, I have to manually clear the summary sheet and then copy and paste from each of the worksheet unto this summary sheet one by one.
View 6 Replies View RelatedI have a file which has a number of sheets each relating to a different project. Each project has a line for each milestone starting at the same row but each project has a different number of milestones.
I would like to create a summary sheet that either pulls through each row relating to a milestone from each project sheet or ideally each milestone that relates to a certain month from each project sheet (each milestone includes a completion date).
I have a tab (All Data) full (column A - S) of daily data, the date is in column A, there's a unique identifier is in column D. The daily data can be repeated multiple days. I need to remove the duplicate data for the unique identifier for the week. I'm having problems with the removal of the duplicate data. This is what I currently have for the weekly (2nd week of the year) data totals:
=COUNTIFS('All Data'!A2:A1100,">"(A2),'All Data'!A2:A1100,"
I am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive.
An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix.
The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time.
The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.
