Summary Report Of Workbooks
Jul 17, 2007
I have been asked to create grand summary workbook that basically copy and paste from a few workbooks in different folders location in a network drive in this grand summary. The sheet to copy from these other workbooks is named as "summary" and the location and filenames are as follow:
P:Section 1RegisterNorth.xls,
P:Section 2RegisterSouth.xls,
P:SectionRegisterEast.xls
The data can be found from A7 to R7 downwards in the "Summary" sheet tab in each of the workbook above.
The data are updated every week and as such I hope to have a macro create to first clear the old data and update the new data.
View 9 Replies
ADVERTISEMENT
Apr 30, 2009
I'm working on some homework for a CIS class, that is pretty straight forward...except I don't know how to make a summary report in excel.
View 9 Replies
View Related
Dec 1, 2006
I have a report that I need to summarize, here is some sample data.
Dealer CodeParticipantModules
F23CH ROB MASSON10
F23CH ROB MASSON7W
F23CH ROB MASSON8
F23CH ROB MASSON9
F32C5 JOHN COUTTS16
F32C5 JOHN COUTTS17
F32C5 JOHN COUTTS21
F32C8 SCOTT PLAKHOLM7
F32C8 SCOTT PLAKHOLM7S
F32C8 SCOTT PLAKHOLM7W
F32C8 SCOTT PLAKHOLM8
Desired results would be column A Dealer Code, Column B Particpant, Column C a summary of Modules taken, Column D a count of Modules
Ex:
F23CH Rob Masson 10,7W,8,9 4
F32c5 John Coutts 16,17,21 3
F32C8 Scott Plakholm 7,7s,7w,8 4
Is there a easy fix using VB code? The report is usually between 1500 lines and 2000.
View 11 Replies
View Related
Jan 3, 2008
I have the following example:
Math Science Biology
A Dave Pete John
B Mary Cate Dave
C Jack Dave Bob
How can I have excel give me all the subjects as well as classes that Dave teaches ie. Math(A), Biology(C) and Science(B)
View 4 Replies
View Related
Feb 11, 2008
columns A,B and C contain the details of each event(race).Column d is the runners in each event and column E a score for each runner. I need to add 5 extra columns with the total score for each event,the maximum for each event,the average for each event,the meadian for each event and the standard deviation for each event. I need all cells filled. see attached spreadsheet. I will have thousands of events.
View 6 Replies
View Related
Aug 16, 2008
I have a spreadsheet of website stats showing the number of visitors to all the domains and aliases we use for company websites. Each domain or alias has its own unique row of data. The data is in the order of most visitors. I have attached a simplified and anonymised example of the data in worksheet "stats". In real life this sheet runs to several hundred rows.
As you can see if look at the worksheet "domain key", each of our websites has more than one domain or alias pointing at it - these are reported separately by our stats package.
What I want to do is find an easily sustainable way of generating a summary report each month, such as you can see on the worksheet summary, which will give a total number of vistors for each site calculated from the visitors to the various adresses each site uses.
What I have done so far is use a very long SUMIF function, e.g. to find all visitors to the FR site the function reads:
=SUMIF(stats!A2:A16, "www.companyname2.fr", B2:B16)+SUMIF(stats!A2:A16, "www.othername.fr", B2:B16)
This looks OK in the example above but in the real data we have in some cases over a dozen domains pointing to one site and its very messy and hard to maintain.
What I would prefer to do is something that would use a range of data for the criteria rather than a specified string e.g.:
=SUMIF(stats!A2:A16, domain_key!C2:C16, B2:B16)
Obviously the straight SUMIF function won't do this. The advantage to this approach is that it would make the ongoing management of which domains are counted for each country a lot simpler as I could just edit the data in the domain_key sheet rather than having update the functions.
Some issues to be aware of are:
The order of data will change each month so youcan't guarantee that each address will be in the same row every monthThere isn't a pattern to the addresses that would allow you to use any kind of wildcard, e.g. you can't say all addresses containing "companyname" are the UK site and all addresses using othername are the French site. Similarly, you can't say all the french site addresses end in .fr - some countries use .com
View 9 Replies
View Related
Sep 14, 2007
I use a specific report on a daily basis which is attached here. Is it possible that that report is generated automatically in the same sequence of rows once the data is updated in another sheet. I am not good in VBA so looking some built in function. I have tried the pivot but all the formatting and design of the report is changed. use the Template wizard with data tracking is used is the above exercise is possible. Is there any other better methord is available in excel. Is there any good web site availble to learn these things.
View 2 Replies
View Related
Sep 19, 2007
I have a project that is quickly growing out of control.
I workbook made up of 14 worksheets. Oct - Sep, Summary and main.
I have been entering all my data in main, which is A - AB. Various types of data, dates, dollars, names, etc. I have been trying to sort the 'main' worksheet into the separate months based on a date in column B.
For example if the date in column B is 1 Aug, I would like the entire row copied to Aug 07 worksheet. Then in Aug 07 worksheet is the formulas to calculate the data needed for Summary page.
Pretty simple, except I cannot get it to do that. I have tried to pivot, auto sort, and a few various VBAs to no avail. One of my Googles turned up this site and many pointers have been found and are close, but most are focused on combining. The workbook is a tad over 1mb so I did not post it yet, but can if needed.
View 9 Replies
View Related
Apr 7, 2008
Not sure if this is possible. From the attached spreadsheet (reduced to only 2 worksheets), i'm trying to create a formula that will find how much an individual trader made for the month (worksheets 1-31 being a month). The problem is a trader will not be at the same stall everyday and may pay by either cash or cheque.
View 3 Replies
View Related
Jun 21, 2014
I need to create a summary report on another sheet, but it is beyond my capacity.
View 9 Replies
View Related
Nov 10, 2008
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.
View 9 Replies
View Related
Mar 8, 2008
i have daily time sheets that make up a week and have 52 sheets for each week...there are contract numbers and contract ticket numbers that i want to use as criteria to sum the total hours of each day and export the data to a sheet that will keep a running total of all hours booked to those contract number and contract ticket number over the coarse of the year as i fill out the weekly time sheets.
View 4 Replies
View Related
Apr 6, 2008
A worksheet called "purchases" contains the list of shares purchased during the period 1.4.5 to 31.3.8. It has four columns :
1. Date
2. Name of the share (name is repeated for every new purchase as it is entered in a new row and is totally independent of the earlier entry of purchase)
3. Qty
4. Sale price
Likewise there is a sheet for sales also. I want to do the following:
a) Find out all the records showing purchases of a particular share and add them up (both quantity and value). And thus, make a list of shares showing aggregate purchase of each share during the entire year. Likewise, I want to do this for sales also.
b) For every share that has been sold within one year after its purchase, apply 20% tax on the profit earned on sale of such share.
Additional information: If 100 shares of A Ltd are purchased on 1.4.5 and 200 shares on 1.5.5 then during the sale of 150 shares of A Ltd on 2.4.6, 100 shares purchased on 1.4.5 and 50 shares purchased on 1.5.5 shall be deemed to have been sold.
View 5 Replies
View Related
Apr 30, 2008
I'm going to try to explain this as accurately as possible. This is my first post, and I'm seriously struggling with VBA, but i'm confident that someone out ther will have done this before.
I have a single workbook which has a single datasheet, and further sheets representing different months of the year.
Sheet 1 ("Data") contains the following columns and data:
DateStart MileageEnd MileageBusiness TripBusiness Mileage
01/05/2008013 get 13
02/05/200813260
03/05/200826100 euro 74
04/05/2008100113 adf 13
05/05/20081131130
06/05/20081131130
07/05/20081131238 alex begg1125
08/05/2008123812510
09/05/200812511288 service 37
10/05/2008128812980
11/05/2008129813110
12/05/2008131113780
13/05/2008137814000
14/05/2008140014560
15/05/200814561490.10
I then have a series of Summary sheets which are supposed to summarise the data on this single data sheetl. But this is where I have issues.
Basically, from the list of data above (running from 1st may 2008 - 31st December 2008), I want to pull out, per month all of the trips in that month. So for May (shown above) I need the date, business trip name, and the business mileage where the business trip name <> "".
To compound the issue, the columns that these are moving into are columns 1, 2 and 6 in the summary spreadsheet.
I have used some example code from cpearsons website to give me a list of nonblank cells in the Business Trip column, and this has worked, but I can't pull the other two columns out effectively.
View 4 Replies
View Related
Dec 20, 2013
How I could generate a report using a Command Button. I have a spreadsheet that contains data in columns, some of which I want showing up in the summary report. Any new data entered should be updated in the summary report when I hit the command button. Existing data in the original spreadsheet does not get erased or written over.
So basically, I have data in column range A:R in Sheet 1. Columns A, D. E, F, G, I, J, P, and R need to show up in the report on Sheet 2. I already have 150 rows of data in sheet 1, so i don't want to start over. So I need to copy the relevant columns, and update the report with any new data that gets entered in the columns in Sheet1, in the next empy row.
I have some vba code that copies individual cells, but I don't know how to do it for columns and for new data.
View 4 Replies
View Related
Dec 1, 2008
writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.
Develop a macro
1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.
2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):
The number of customers is dynamic i.e.it keeps changing every month
4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.
5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H
6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
View 9 Replies
View Related
Mar 3, 2014
We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
View 3 Replies
View Related
Nov 14, 2008
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?
View 5 Replies
View Related
Apr 8, 2014
Im currently working on making a workbook as a summary/join of several workbooks. All cells are formatted in the same way, and only Row 1 is the same in all workbooks.
How to combine several excel files into one file?
My issue is that when i run this macro, the rows are overwritten for every workbook runned through.
I end up with only the contents of the last copied workbook.
I guess my EndRow is wrong, or the copy function, since the offset is not set properly.
Code:
Sub ExtractData()Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Cnum As Integer
[code].....
View 1 Replies
View Related
Aug 14, 2009
I have a set of workbooks, with multiple sheets within each which I receive each month from field units. The formats are identical.
What I want to do is to extract data from one column on one specific sheet within each workbook to a summary sheet on a new workbook. In the snapshot below, I would like to collect data from the "Actual" column (in yellow) and then paste it on a summary sheet.
Monthly reportingEntity 13. Risk reportingJanuaryMarket Risks - LoansJanuaryTargetActualVarNumber of competitors 110%Market share 1%1%0%Ranking in market 110%Concentration of portfolio – region-wise (number)1%1%0%Concentration of portfolio – region-wise (value)1%1%0%Market growth rate 1%1%0%Inflation rate 1%1%0%
This would appear in a new summary worksheet as below: (the cell labelled "Entity 1" would then appear as the column header in the summary sheet.)
Monthly reporting - summary sheet3. Risk reportingTarget rangeEntity 1Entity 2Entity 3Market Risks - LoansActualActualActualNumber of competitors 1Market share 1%Ranking in market 1Concentration of portfolio – region-wise (number)1%Concentration of portfolio – region-wise (value)1%Market growth rate 1%Inflation rate 1%
The steps I imagine are:
1. Open all workbooks in a specified folder (folder name upon prompt)
2. Search each workbook for a sheet titled "Risk Reporting"
3. Copy the specified cell (for the column header) and the specified column into a new sheet in a new workbook
4. Move on to fill up the next column, and so on ...
Also is there a way to order the copying so that the columns always line up in a specified order? (e.g. Entity 1, followed by 2, followed by 3 ...). One way I guess is to fix the column headers in my summary sheet, and then fetch the data from the corresponding worksheet, by matching the names.
View 9 Replies
View Related
Mar 26, 2013
I have created a template workbook for individuals who work on different sites. The spreadsheets used by each individual have the same headers. The sheets all sit in the same folder in dropbox.
I am wanting to produce a summary workbook within the dropbox folder that automatically populates when an individual updates their workbook.
I have attached mock example of the individuals worksheet for your information.
The questions that I have are as follows:
1. Is it better within an individuals workbook that they record their activity all on one sheet as opposed to having a spreadsheet per months activity? It seems tidier to have a sheet per month but does this make it more difficult to produce a summary workbook?
2. The individual is required to record a patients individual drugs on separate lines but they need to record a unique identifier, age, *** and month worked. Do they need to record all this for each line of drug or is there an easier way?
3. How should the summary sheet be produced - pivot table or sheet with formulas linked to the individuals workbooks?
View 3 Replies
View Related
Jun 11, 2014
I am attempting to write code for a macro that can cycle through about 30 excel workbooks and merge the data. getting the raw data on one sheet is not a problem, but I also need to sort similar data into corresponding columns while merging such that all data from the workbooks with a certain column heading will be in a single column with a single heading on the summary sheet. The problem is that the column headings on the source workbooks are not always standardized, i.e. "F high", "high F". Each sheet has several columns that look like the one below, with a header at the top and a series of numbers. (please ignore the dashes, I just used them to line up the columns in this post, in excel each value is in a different cell)
F Low---F High---V Low
721-----999---804
721-----999---0
721-----999---0
711-----720---786
711-----720---713
My vba skills are very basic, so I have been approaching this problem with a mountain of if..else and InStr commands, which doesn't seem like the best method and isn't working.
View 3 Replies
View Related
Dec 23, 2013
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?
View 2 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
Jun 26, 2014
I have 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving
then doing the same for Workbook B, etc.
View 5 Replies
View Related
Dec 27, 2009
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.
View 3 Replies
View Related
Sep 4, 2008
i need a macro that can copy info from all tabs (30 tabs) (B1:ZZ500) and put them into a sumamry tab.
View 9 Replies
View Related
Feb 4, 2010
I am trying to summary my differentday food data. i want to add total in SUmmary table.
View 6 Replies
View Related
