Attached is a sample of the report i am trying to create. I am after a way to sum a range of cells between a chosen date range, firstly as an individual count in report #1 and then in a combined count under report #2. I have placed some text bubbles to give an idea as i am finding it hard to explain but i can see the whole picture.
After all the trials and fails, this is the last part to be completed........
Sample.xlsm
I have to create a report that captures the work of 2 different resources on each day of the month, the sheet i am working with has 3 tabs - Main (this houses the main report, with identical fields for each resource), ABC - for details pertaining to work done by the resource ABC ... and a tab called XYZ for details of work performed by XYZ. A resource can work on multiple projects and 3 different modules in one day.
So for each date, i need to bring in the details for columns .. Project Name upto the column # of FB's, for each resource that is for ABC and XYZ.
I have attached a file with sample data, it has the main tab with what it looks like before the data is pulled from the ABC and XYZ tabs ... and what it should like after the required data has been pulled from the ABC and XYZ tabs.
What i am looking for is the formula that i got to enter in the main tab in order to pull the required data from the ABC and XYZ tabs for each date mentioned in each of the columns for each resource (that is ABC and XYZ) in the main tab.
Average.xlsxHi
I have changed the attachment to use CSV's
I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.
What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.
Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.
Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1
I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)
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.
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 Relatedfind the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View Relatedi have two fields with dates - one field A1 for date authorised (for a data request) and one field B1 for date actioned (data request)
i need a formula to populate in C1 the following:
if A1 is blank then C1 is 'not actioned'
if B1 is blank then C1 is 'not complete'
if both contain dates then C1 to calculate the number of working days between the dates eg. A1 10.08.09, B1 11.08.09...C1 = 1 working day
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.
I was going to create a summary page and reference column d.
The following just gives me the min, I want the min average if possible:
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?
=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)
I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.
so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.
I am working in SAP, I am attempting to convert a report in excel and I'm get the following error codes/debugger messages:
1ST Error Message is : Path/File access error: 'VBD1.tmp'
Then I press okay and get a Visual Basic Error:
Run Time Error '1004'
Methon 'Copy' of object'_Worksheet' failed
If I press debug I get a bunch of Visual Basic sheet, with the following highlighted
Set ws = SAPSheets.item(SheetIndex)
to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.
View 9 Replies View RelatedI am trying to customize an aging report, column will be 30, 60, 90 120 + I would like the amount to show under each column. The charge amount for $252.38 each day.
View 9 Replies View RelatedSo this is the problem, there are 65 files that are stored in a folder, each file has 8 tabs, all these tabs within each file are identical in every aspect ( i.e cell ranges and names). The max number of rows in a tab is 81. To break it down:
Tab 1 : Instructions (this will never change and will always be constant among all 65 files)
Tab 2: Row 1 will be the same for all files, rows 2-9 i don't need, except for the info on cell C3 which I need to be copied through S11:S33. S10 needs to equal A3 with the format of R10. And then just have the next files tab info below this table with the same logic being followed.
Tab 3: Same logic as tab 2 but now the range is from S11:S53.
Tab4: Same logic, just copy and paste tables for all files, and doing the same thing with column S hat was done in tab 2 in order to be able to filter tha data.
Tab 5: Same logic, with H1 being filled by the descriptor on Tab2( This will enable one to filter though the information since the descriptor in cell 3 from tab 2 is the short name given to each files which differentiates the data.
Tab 6; Same logic
Tab7: Same logic, but would like a drop down box on right to filter by service centers.
Tab 8: Same logic, but would like to have drop down on rigt in which I can select the given short name ( i.e cell 3 from tab 2 of each file)
This way I can have one master file which encompasses all the information from all 65 files in one file and be able to sort through the data in each tab by the short name description. I need a macro for this since these files get updated in a weekly basis.
Any help on writting such a code would be greatly appreciated, my excel VBA skills are non existent!. I have attached the template of how one of these files looks like. There all the same except with different names of course and all are stored in one folder.
sometimes pivot tables won't give me the details I want the way I want. any time my boss asks me for a monthly report I have to do everything manually from the pivot table and insert the data on another table where I put the filteres data. I want to be able to get a monthly sales report per store that will populate itself in a daily basis, I also want to be able to select a store, style, size and date from a form in Excel and see the report in a sheet. Aproblem that I have found in order to use the size as a parameter is that the size comes in the same line as the item description and do not know how to make excel to check just for the size in the description field.
The attached file contains sales data for a better understanding of my case.
I have a coulmn with results in that are in a decimal point example .01, .02.
At the bottom of the coulumn I want to report the max number and the min
number but it does not. Also in one column my results are listed as <0.05
etc. At the bottom of this column I want to report the max and min but it
does not. I believe because they are not whole numbers. Is there a way to
report these numbers. I tried an IF formula where if <0, max(i12:i36) but
that did not work.
Analysis of Profit is very impt in business , that is why i keep trying to find way to use excel to generate profit report.
I have been trying to solve this for last one month. even when i bath... But no result.
May i ask any one here know how to convert my data to a report using . Below table said every thing.
Project (10) HIJ1My Raw Data File 2BrandMonthProfit In %3Brand AJan 0730%4Brand BFeb 0750%5Brand AJan 0730%6Brand BFeb 0750%7 8 9I Need Report In New Work Sheet 10 11BrandJan 07Feb 0712Brand A30%30%13Brand B50%50% Excel tables to the web >> Excel Jeanie HTML 4
i have accumulated a lot of data on an excel sheet now i need to be able to run 2 reports that will spit out the data in a specific way but i have no clue but i have made a word document to show how i would like it to spit out
View 9 Replies View RelatedI'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 RelatedI've three columns excelsheet and want to create Per day report.
Date,Records,Status
I want to count per day records and status.
This will be self-explanatory when you check this screenshot
1- I 'm working with a table wit many Sheets.
I want to make a Report Sheet with selective ranges(cells)
the selective cells should paste as link ( in order to be updated cells)
2- then how to Zip this worksheet to zip file ( to save it)
3- If I have a Macro and i want to perform this macro to all sheet except one sheet how to that ?
Just to fill you in on what I am doing, I am am putting a report macro in my spreadsheet that looks to see if a value meets the criteria if it does then it will copy a selection of cells data from that sheet into a new sheet. It will do this for all the sheets so in theory I should end up with a simmilar kind of report function to access. The problem is to when I do this the data does not appear, I do not know why.
Private Sub cmdPoor_Click()
Dim sheet As Worksheet
For Each sheet In Worksheets
If Not sheet.Name = "Main" Then
If Not sheet.Name = "Template" Then
If Not sheet.Name = "Help" Then
sheet.Select
If Range("L44").Value = Poor Then
Dim strName As String
Dim strSurname As String
strName = Range("L41").Value
strSurname = Range("L42").Value...................
As it is now I use several loops to scan one sheet to collect the data and paste it onto a second sheet. The first sheet has several rows of data, denoted by one of four colors, that I have to collect and put into a report grouped by color. Each color represents the current status of a part of a project. The while loops stop on no data in column one. So each loop runs down the sheet, looks at the data row color and rejects it if it is not the right color for that section of the report. I am sure there is a better way to do this. I was thinking maybe create a temporary data base, collect the data adding fields for the current status then use a sql for each section of the report and insert rows as required.
While Worksheets("All Work"). Cells(iGetRow, 1).Value <> Empty
Set Cell1 = Cells(iGetRow, iGetCol)
Set Cell2 = Cells(iGetRow, iGetCol + 4)
Set rCurrent = Range(Cell1, Cell2)
rCurrent.Select
With Selection.Copy
If rCurrent.Interior.Color = Purple Then 'Purple is a constant
Worksheets("Report").Paste Destination:=Worksheets("Report").Cells(iPutRow, 1)
iPutRow = iPutRow + 1
End If
iGetRow = iGetRow + 1
End With
Wend
Data Structure:.............
have a data set with client id and most dates of activity....
I would like to be able to include within a report the date of first activity and the most recent date of activity....
please see attached sample worksheet...
I have a list of equipment and bits that go with each kit and depending on other kits which they are installed with.
I'd like to automate the list.
Attached an example: I'd like excel to look for the first piece of equipment over their respective sheets and list the parts needed for the chosen equipment.
I have four columns with data. I need to Correlate columns 1 and 4. eg. Column 1 = All instances of Column 4. I must do this by matching the values of Column 2 to Column 3. There will/can be multiple instances of the same values in all columns.
View 1 Replies View Related