in column A, i have a dates listed, in column G, the transaction amounts that correspond with those dates, i want to know how to run a report on this. what i need is to auto sum the amounts for a specific month without having to create a new spreadsheet for every month. IE - what are the sales for september? how do i do this?
View 2 Replies
I am working on building a better report manager using named ranges
1.) Loop through the workbook and find all named ranges
Output the named ranges to a worksheet
2.) Read the values from the worksheet and print each one
Seem simple enough and is really just first step
Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed
But I'm getting ahead of myself.
First is to print a named range
My code is below
Error msg:
Run Time Error 91
Object variable or With Block Variable Not Set
Debug points here:
Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value
1. Loop through all named ranges and output to worksheet. This part works fine:
Option Explicit
Sub GetNamedRange()
Dim nName As Name
Dim wb As Workbook
Dim wsPrnt As Worksheet
Dim Row As Long
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
i attached link to sample and its contains employee sheets and Report sheet ,and in Report sheet there is comment
[URL]
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
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 report that gives me dates in a format that's mostly useless. I am sure there's a simple formula I can do each time I run the report, but I'm not sure. My report gives me dates like this...
View 4 Replies View RelatedI am trying out a new spreadsheet to log my cycling. I have attached the example so far. I am going to have a form appear and fill it out. Upon clicking 'Add' button I want a macro that will search column B (a list of dates) and match the date the users filled out on the form with the spreadsheet and then paste the corresponding form data in that row.
How would I accomplish this? Does the formatting of the date field complicate this since on the spreadsheet I display "Saturday 1/08" and on the form the user enters in "01/08/2008"
I have a spreadsheet which is used for tracking work items for a team of people. On one sheet I have a column of dates and rows of named 'tasks' against them. I need to identify the first and last dates at which a 'task' is listed.
My spreadsheet is attached.
On sheet2 K2 I want to see the first / earliest date where 'Fish' occurs on sheet1
On sheet2 L2 I want to see the last / latest date where 'Fish' occurs on sheet1
I have tried using SUMPRODUCT MAX / MIN but I got myself in a muddle!!
TrackerRH.xlsx‎
I am trying to COUNTIF some data from a spreadsheet I have, where they have been entered after a certain date (which is part of the information). The problem I am having is that I am trying to enter the date I want as a variable in another field so that it can update the rest of the table.
Basically I have a field where it says "Calculate new business since" and then a date should be entered - in turn the formulas should make the calculations based on that date. Is this not possible?
I have attached a sample of my spreadsheet to use as a reference. I suggest you open that up and give it a look through to get an idea of what's going on.
What I'm looking for is a way to create a report (the quickest and easiest way of course, as always) which will give the total for the columns 'Exit Weight', 'Net Weight' and 'Entry Weight' sorted by each company between the periods of the 25th of each month to the 24th of the next month (the Dates are in Column A obviously)
Below is an example of the output I am looking for:
Company: Period: Exit Weight: Net Weight: Entry Weight:
EASTWELL HA.. 25/10/07-24/11/07 102.2 124.3 200.14
25/11/07-24/12/07 143.7 185.2 250.18
25/12/07-24/1/08 ... ... ...
BOURKE TRANS.. 25/10/07-.... ... ... ...
the totals for the Exit Weight, Net Weight and Entry Weight are not the actual totals, they were just put in to give you an idea of what I'm talking about.
And on and on. If there is a way to automate this with Excel VBA and some sort of code that you could give me to plug into my spreadsheet I would be very appreciative. I understand this is a bit complicated and would probably require a few steps so if don't get the answer I'm looking for I'll probably find a way to split it into steps and then ask the questions seperately.
I have 2 sheet in MS Excel (MATTER & REPORT) . Database included at sheet MATTER. Report will be seen on sheet REPORT based on entry date. What formula used to produce report automatically.
View 4 Replies View RelatedI am looking for code that can generate the report that a user selects.
For example the user clicks reports on the userform. The report userform opens up. The user checks the monthly report button and enters the last day of the month he wishes the report to be for. i.e 7/31/2010 and then clicks generate report. when executed excel creates a new workbook and creates two sheets. the first looks exactly like the master data sheet from the original worksheet but of course with the specified data. the second sheet will populate a summary of the data. I will address that after i figure out how to get the first sheet compiled.
I am trying to loop through some data in Column B and I want it to report on ALL values that are < todays date. However the loop is stopping at the first date that is less and not continuing on afterwards. I know what is causing this but I am unsure how to solve it.
View 2 Replies View RelatedI have a workbook with 12 sheets for monthly results and a Year to Date sheet. For presentation reasons, the monthly results are listed as seen in the first three worksheets, but I want the YTD results to be structured as seen in the fourth worksheet. I have shown how I am currently achieving this in the "Call" section of the YTD sheet, but as there are 12 months and many more variables in the original workbook, I'm wondering if there is any easier way than the one I'm using.
View 4 Replies View RelatedI have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,
I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.
I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.
=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")
It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.
The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.
Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!
I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.
I hope this becomes a bit clearer on the sheet I have attached,
I have a table with three columns. I'm building a calendar on a separate worksheet and am looking up the "value" based on a calendar date. So if a date falls within any of the ranges, I'd like to return the value in column C. For example, if the date is 02/07/12, I'd like for the result to be value 1, or if the date is 04/17/12, then I would like the result to be value 3. I've used a nested vlookup, but all that give me is the value when either the start or end dates match, but I can't get a value when the date falls within the range. If the dates were consecutive, I would simply use vlookup/TRUE, but the dates are not consecutive.
ABC102/06/1202/09/12value 1203/12/1203/15/12value 2304/16/1204/19/12value 3405/21/1205/24/12value 4506/25/1206/28/12value 5606/25/1206/28/12value 6
Please see attached spreadsheet on what I am trying to achieve.
In the data tab I have my base data, which is dates across the top and names down the side. I have information in the middle such as 'H8' which means that the person was on holiday for 8 hours on that particular day, and 'S8' which means they were sick for 8 hours on that day.
I want a macro that will rearrange the information in the table and put it into the report format which is in the report sheet.
If you see the spreadsheet you will probably get a better idea of what I am trying to achieve.
I have a pivot table with the following items;
Report Filter = Project Names & Dates (filtered on 2 fields)
then the pivot table of data shows
Dept, Sum of Hours, Sum of Total ($), Sum of Days
When you click on the drop down to adjust the filter for the Date it gives you the whole list of dates, day by day as it is in the source data.
Is there a way to make it in the filter by month and year, the way it would in an ordinary filter. So if I wanted to have the pivot table show only the values with an October date I can, rather than having to deselect all and then individually check the boxes for the 1st through to the 31st of October.?
I have looked through the forum and found pits and peices but can not put the puzzle together. Found the VBA code in my example from a earlier post but there was no final answer to the post.
Trying to have the user put in a date range via command button. Fro mthis date range the data thats falls within that range is copied to a report sheet. Will also need to have all the old data from a earlier querry removed. Have attached example sheet.
I have a long list of data in a table. the first column is a date&machine. in another column there is a comment field. To find the first occurance of a date/machie and report the relavent coment I have been using a vlookup; this works well. Is there any way to find a second or third comment for the same date & machine?
View 2 Replies View RelatedI 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 RelatedWhat i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
View 9 Replies View RelatedI want to have a new macro/button to redo the chart cells based on the dates in cols T and U (abt 400 entries). T has the start date and U has the end date.
Row 9 - X9: CJ9 have dates for about 10 months (weekdays only)
Column J have either of these two values: A or B
Column C has either of these two values: C or blank
I would envisage it going through each row, checking:
If date at top of that col was between the dates in cols T & U
then
if col J = 'A' then set cell to black block
else if col J = 'B'
then
if col C = C (closed) then set cell to red block
else set cell to blue block
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View Related[URL]
take a look at the attached example and let me know how to make the formula work?
For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.
In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".
Sum based on date ranges. I am creating one logical formula.
I have an excel sheet with two columns.
A1: Date B1: Time Spent
Date
Time spent (in Minutes)
Total
[Code].....
As per above figure I want to sum based on the same dates. I.e A2 has date 1/26/2012 for which first entry is 12 so total will say 12 in C1, for the same date second entry is 14 so 12+14=26 is there in total at C2, for the third and last entry of 1/26/2012 it's 16 so total would be 12+14+16 = 42 in C3 which is grand total for 1/26/2012. I want to fill entire C column with some logical formula so that the logic holds true for various dates.
I have tried to use the IF formula like =IF(A1=A2,SUM(B1:B2)) but it fails at third cell. I have tried several ways to do this but no luck so far ....
I have start dates (column A) and maintenance expiry dates (column B). What I want to do is set up a formula to see if the product was renewed between 01/04/2007 - 31/03/2008 and return a simple yes, no or new_sales.
Examples
If the start date was 26/09/2006 and maintenance expiry date was 26/09/2008 then it would return a yes that they have renewed
If the start date was 18/04/2006 and maintenance expiry date was 18/04/2007 then it would return a no that they have not renewed
If the start date was 20/06/2008 and maintenance expiry date was 20/06/2009 then it would return a new_sale
I have one worksheet with 52 rows for each week of the year with the
following headings: week #, start date of week and end date of week. I have
another worksheet where I need to forecast expected expenses for new
employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
then calculate expected spend based on remaining weeks in the year. I do
this manually right now. How can I automate this?
There is a database with work plans for a project cycle, which contains employee names, types of activities, planned working hours for each activity (not in time format, just number) and dates when these hours will take place. Each employee may have one or multiple activities and each activity has different number of hours planned to complete and different dates. My aim is to calculate the number of hours planned for each employee (to sum all activities) within a certain period of time.
So, what is the question? I need to calculate the number of workdays within the intersection of the planned period for the activity and my given period of time.
I'll give a small example to illustrate what I mean.
I'm calculating planned working hours for this week, which is 11/23/2008-11/29/2008. One of our employees, let's call him John, is going to work on three activities, A, B, and C. We have the following plans:
Activity A - 16 hours - 11/24/2008-11/27/2008
Activity B - 45 hours - 11/15/2008-11/25/2008
Activity C - 10 hours - 11/26/2008-12/01/2008
As you can see, activity A is going to be complete within this week, it's perfectly fine. Activities B and C should be counted separately, because they include days earlier or later than this week. Is there any way I can easily geet the number of workdays that are included in the plan period and this week?
Another thing to mention is that I'm working in Excel 2003 and it's not acceptable for me to install the Tools Pack, because I need this firmula to work on other employees computers and there is no chance they all will install it too.
