I am using Excel 2007 and need some help. If you could please download and open the attached excel sheet then i guess you will be able to understand my problem easily. I am also not sure if what i am asking is really possible or not in Excel (would prefer without any VBA coding but if there's no other way then VBA coding would also do).
What i need is that based on the data from 'data' sheet i want to dynamically fill the ' Date' column in 'CatA' sheet. If you check the 'Expected Result' sheet where i have manually filled the dates and automatically calculating the 'Amount' but i was curious to know if somehow i could dynamically fill the values in 'Date' column based on month name in header?
I would like to calculate the sum of investments based on their expiry date and have the totals per month (and year). I have a table that looks like:
TermInterest rateMaturityBalance 24 Months7.12%11 November 200740,000.00 12 Months7.74%13 November 200750,000.00 24 Months7.05%10 January 200853,889.12 12 Months7.85%11 January 2008120,000.00 12 Months8.02%22 March 200817,000.00 36 Months6.68%30 June 200832,000.00
I'd like to have something like:
Nov 07 90,000.00 Dec 07 0.00 Jan 08 173889.12 and so on...
Admittedly I am an Excel novice, so excuse me if my question is dumb and has a simple answer (actually I hope it has :-) but I have tried to find a solution by searching forums, my books, online help, I tried my luck with sumif and SUMPRODUCT functions, even used the conditional sum wizard, but I can't get it right
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.
Every month I need to consolidate the individual report in a single sheet for that I was doing with copy paste options but I need this to happen through macro or some other short way.
I have been asked to create a report that rank top sales agent for the month with the following variables:
Total # of sales-35% Total $ of sales-45% Number of calls made-20%
Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
I am trying to arrange data (pay by week) for regions and sites within a region. In the first Report filter, I select the site, and the second report filter gives me all of the sites - not just those in that region.... How I can either arrange my data or change my pivot, so only the appropriate sites show under the region?
I have a pivot table based on sales data and I need to know the 3 salespeople that had the highest sales during the current fiscal year versus the last fiscal year.
I have tried manipulating the pivot table but I keep getting the error message: "the following system error occurred: the requested name is valid but no data of the requested type is found".
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.
I have a simple table Attached. (A simplified version of a much larger table) The data continues on for thousands of entries and there are quite a few more columns. Names are dynamic in that names will be added and deleted over time. (There is a list of names on a different worksheet that updates as users are added.)
I wish to Consolidate the Data Under Different Titles. The data in the NAME column will Consolidate to list each name in Alphabetical order. The data under the RAP COUNTER column should count the number of YES entries per NAME under the new title ATTEMPTS. The data under the RESULT column should Count the number of KILL entries under the new title KILLS. And the Data in the TG SCORE column should average all entries per NAME under the new title TOTAL SCORE. I tried to do this with a pivot table to no avail. I could write some programming to accomplish this, but I am am amateur, and I also worry about time required to run a macro for thousands of lines.
I have a 5 column LIST that will have new rows added daily. Column A= Date (mm/dd/yy), B=id (s or u), C= ArriveTime (hh:mm), D=DepartTime (hh:mm). E is Delay time (D-C). Arrival and Departure times will vary from 5 AM - 9 PM but will always be on the same day. I would like a count of all s with delay of 0-6 min, 7-12 min, 13-18 min, 19-24 min and 25+ minutes. Also count all u with same delays. I should get 10 results (1 of each) using the following data. Would also like the mean, median, mode, min & max for all C (Arrival), D (Depart) and E (Delay) times.
1/1/08 s 08:00 08:01 1 1/1/08 s 08:00 08:07 7 1/1/08 s 08:00 08:13 13 1/1/08 s 08:00 08:19 19 1/1/08 s 08:00 08:25 25 1/1/08 u 08:30 08:31 1 1/1/08 u 08:30 08:37 7 1/1/08 u 08:30 08:43 13 1/1/08 u 08:30 08:49 19 1/1/08 u 08:30 08:55 25
I am trying to create a pivot table report to find out how much profit each client has made and I need the total profits to be added up for each client so I can see who has made the most/least.
In order for me to create a filter list on my worksheet, I'm having to first delete a querry report that's being stored/generated from the data being imported into my workbook. Due to Run-Time Error'1004
A list cannot overlap a range that contains a pivotTable report,query results,protected cells or another list.
Initially, I only had 1 ActiveSheet.QueryTable to delete ("IN Stock Status Report - 6896_12"), but this also changed when I ran on another PC under someone else's account.
I have created a pivot table and I'm using report filters for both Client and Asset (obviously there are a lot more data points). When I filter on Client 1 I would expect to only see Port 1 - Port 7 available in the Asset filter however, I see every asset in the data set. I need to pick and choose using the Select Multiple Items check box without having to scroll through every single asset. Is there a way to easily do this?
So I was wondering if it were possible to completely lock a pivot table, almost like protecting the sheet, but to allow the report filter field at the top of the table to be used. So what I'm trying to show is our net rev accounts in the rows & quantity and amounts as the value all fixed and unable to move about or drill down; but then have our product titles at the top in the report filter which you can still use to be able to select specific products.
I understand that I could make a big old page of drop down lists and vlookups and sumifs etc etc, but surely the above would be way quicker, plus I wouldnt have to bother checking it all works regularly!
I'm currently running this macro, which nearly does the job, but the data and row labels are still able to be manipulated. So... changing the macro, or doing something completely different?
Sub KillPivot() Dim pf As PivotField With ActiveSheet.PivotTables(1)
I have a pivot table created from a data table with three columns: Date, Sales, and Customers. I have the Date column in the Report Filter and I want to change the date based upon a value in a cell range named Date, of all things. The pivot table is located in another worksheet and the range Date is changed by a spin button in the active worksheet.
My code is:
Sub ptDate()
Dim pt As PivotTable Set pt = Worksheets("Pivot Tables").PivotTables("PivotTable1")
When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number.
Is it possible to preserve the format of the raw data file that has been used to create the pivot table output?
For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost.
I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up.
If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.
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.
I am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want.
Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel.
Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.
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.
I have a big list of names of people that have completed a bunch of training they have completed. I want to be able to run a pivot table/chart and only count a user once vice each time they have completed a training. So in otherwords I want to get a count of how many people are on the list vice how many certifications they have.
I'm trying to find a macro that will report how many values are returned in a pivot table. For example, if there is 5 values returned, I want it to return the value "5" in a cell If there are 10 values returned from the pivot table, I want to return the value "10" in a cell
I was wondering if it is possible to create a custom pivot table report filter? I would like to take an existing pivot table report filter and manually add values into it. I would like to do this because I have multiple pivot tables, some with the same values and some with different values and I have a VBA code from Contextures that applies a mass filter to all fields with the same name. So if i could manually add values into one report filter, I could filter from one location and have all my pivot tables update at the same time if they contain the value that i would like to filter by.
I have a file with lots of pivot tables which works fine. But if I save and reopen the file then I get the following message when I try to change a filter on any of the pivot tables:
"The pivottable report was saved without the underlying data"
(ideally without refreshing all pivot tables using a workbook open event as this will cause a 10-20 second time-lag)
p.s. the file used to work fine before I added an extra pivot table (there are roughly 10 pivot tables in the file)
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
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
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'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.
When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)
I want to create a report using power pivot while creating the relationship between the linked tables, power pivot is throwing error "The relationship cannot be created because each cilumn contains duplicate values. Select at least one column that contains only values"
