Excel 2007 :: Doing Advanced Stop With Pivot Tables?
Aug 9, 2014
I am new to doing advanced stop with pivot tables. I know how to make one to do very basic things but I have since learned you can add your own calculated fields. I have a list of data that has a line of record for each different day of sales which each guest. There is a lot of information on the sheet but the columns that matter are as follows:
ColA= CustID
ColB= Trips(this field is the same value for each record of the same customer and represents the total number of trips the Cust has) on a side note this field can be removed if it would be easier to just do a count of custid as those are the same thing
ColC= Months(this field is the same value for each record of the same customer and represents the total number of different months the cust made a purchase)
ColD= AmountSpent
I can use a pivot table to show me the total amount spent by Cust real easy. But I would like to add in the two other fields Average per trip and average per month by cust. Is this something that can be done in a calculated field?
I use Excel 07. I know I can run formulas to get the answer I want however due to the shear volume of records being over a million(I know we should use access at this point but the company does not want to) the time it takes the formulas to calculate freezes my computer(need a serious upgrade).
it's possible to set up conditional formatting to compare values between two identically-formatted (i.e. same row and column) pivot tables?
I have two pivots from two different sources and I want to be able to visually identify discrepancies between the two. Both have dates as the row labels (same date range) and categories as column labels (some categories may not necessarily be present on both pivots but the format of the labels - i.e., the names of the categories - is consistent where there is overlap)
I've tried doing this with 'traditional' CF but whenever I manipulate the pivots (i.e. by selecting / deselecting an item in a report filter), the movement of the columns trashes the formatting and I have to set it all up again.
Figured, given the structure of the tables is essentially the same, there might be a smarter way of doing it (by referencing the pivot fields in some way)
I have the following code and I can't work out how to fix it. What i want is for the macro to create the pivot table from the "used range" in a worksheet but I want it to be formatted as a 2007 pivot table. The reason I say this I because I have some 2003 code which works very well which is where the idea of this come from but it doesn't seem to work here. I have highlighted the problem in orange, I know the rest of the code works because when I replace the orange bit with
"Raw Data!R28868:C33" it works.
Sub CreatPivot() Dim wksdata As Worksheet Dim rngdata As Range Dim wksdest As Worksheet Dim pvtTable As PivotTable Set wksdata = ThisWorkbook.Worksheets("Raw Data") Set rngdata = wksdata.UsedRange.........................
If I create a Workbook in Excel 2003 with Pivot Tables when I open it in 2007 the Pivot Table are no longer there. It look like the Pivot has been converted to Data only. Are there any ways of correcting this?
I am creating many tables in excel using a macro, and I want to copy them into a word document for later use. All the tables come from a long list, so for simplicity, they all get created on the same range starting at F1. Before deleting the existing table and adding the new one, I'm trying to copy/cut the table and paste it into a word document.
In case it makes a difference, I have office 2007
VB: Sub ExcelToWord(LastRow) Dim objWord As Word.Application Range("F1:F" & LastRow).Copy With objWord .Documents.Add .Selection.Paste .Visible = True End With End Sub
I am trying to create a dynamic sub-table on another sheet from a master list in excel 2007. Where the master list size can change and have this reflected in the sub-table. I cannot use pivot tables.
Example: Master List (Locations): London Drummond Kentville
Sub List (should look like this): London #count Drummond #count Kentville #count
I have 2 separate tables.. both a list of contacts of sorts.
In one table I have a list of all my clients (table A), in the other I have a list of "preferred" clients (table B).
The contact details etc that are stored in table A are more comprehensive to those in table B.
Effectively, I want to search for all of my preferred contacts from table B and filter for them in table A.
Then ideally I will sort alphabetically and just cut and paste the details into my preferred client spreadsheet.
I had a look online and there was something about creating a "relationship" but I don't think excel 2007 allowed me to do it. I certainly hadn't heard of powerview or powerpivot that they were using!
I saw a previous post regarding using "with (nolock)" in the FROM statement with each table to prevent SQL from locking the tables. I'm not sure exactly what's going on, but when I edited a query to add an extra criteria, the query is grinding the server practically to a halt (such power!!). Our IT guy tells me that my query is taxing the server. Maybe I'm not using the NOLOCK correctly?
Here is the query as I'm running it that runs slow. (The additional WHERE statement that I added that created the slow down is this one: ((cicmpy.AccountTypeCode='TSH') AND (oehdrhst_sql.ord_dt>={ts '2013-01-01 00:00:00'}))
Prior to adding this statement and the NOLOCK, the query runs in a flash.
In Excel 2007, I have a tables linked from Access 2007. I frequently delete and import a new set of data into Access, then refresh the Excel file. Most of the time this works, but I've noticed sometimes one column doesn't refresh (even though I can see it in Access). If I modify the field name in the Access query, then refresh the table in Excel the newly named field is added in the in the last column of the table with current data. The column that wasn't refreshing stays the same.
When I originally created the link in Excel, I added various columns with formulas, but do not edit the columns that are imported from Access.
Using Excel 2007.I have references set for Microsoft ADO Ext 2.8 for DDL and Security and Microsoft AciveX Data Objects 2.7 Library.
I am trying to refresh tables in Access dbase from Excel.
I am receiving this error:
Run-time error '3709' The connection cannot be used to perform this operation. It is either closed or invalid in this context
Debug points here
Code: Set adoTbl.ParentCatalog = adoCat
what I am doing wrong?
Full code below
Code: Option Explicit Sub RefreshLinks() 'Comments: 1.)Refresh linked tables ' 2.)Set Reference To Microsoft ADO Ext. 2.8 for DDL and Security ' 'Date Developer Action '--------------------------------------------- '02/01/12 ws Created
I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;
I use Excel mainly for creating & updating simple logs, and the problem I keep running into is that when I click in a cell, it will automically select & highlight multiple cells below the one I am trying to work in. It will select anywhere from 4-5 cells up to 13. I don't know what this function is, and I don't know how to fix it. But it makes it very difficult to navigate and cut/paste or change a formula.
I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database
When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.
Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value?
1. Age (up to 20, 21-30, 31-40) 2. Gender (male, female) 3. Location (London, Paris, Berlin)
I want to see Age and Gender as column labels and Location as row labels. However, I don't want Gender as a sub-set of Age - I want Age Labels followed immediately by Gender labels ie:
Upto20 / 21-30 / 31-40 / Male / Female
I don't want: 21-30 Male / 21-30 Female / 21-30 Total / etc etc..
I have a pivot in excel 2007 but I have the following problem; I want to exclude values from one variable that have a value lower than zero or blanks:
Month #AVG Employees Jan 200 Feb 171
But the problem is, if i filter on the variable by placing it in the Report filter (select multiple items to remove blanks and -999), when i refresh the pivot next time for the new month it won't add the new values for the next month. How to have the filter so it applies the criteria but allowing new values in for the next month.
In Excel 2003 I created a macro that copied data from a table, pasted into a new sheet, created a pivot table, copied and pasted that data next to pivot table, sorted, copied and pasted into chart data. I've used this macro in Excel 2007 with no problems.
Now I'm trying to create a similar macro but it won't complete the pivot table. My Pivot Table Field List should have the Row Label and Values populated but when I run it all that is populated is the Values field.
Is there something in 2007 that will not allow me to create a macro using a Pivot Table or am I doing something wrong? The code is below if that helps ...
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.
Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.
Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.
I have a pivot table that summarizes jobs that need to be completed on a specified date. I need to put that information on a calendar, but I would like to concatenate all information from that date so I can do a vlookup from the calendar. My other problem is that the number of cells change for each date, so I cannot specify exact cells, (some only have 1 entry, but others may have 4 or more). Can I specify a range of cells based on the result of the pivot table?
I have attached the pivot table, calendar and the data sheet with the vlookup info. Am I using the proper calendar or method to find this info?I have struggled with this for over a week now.
I have created a pivot table using a family name in row labels. The names appear in alphabetical order unless the person's name is the same as a month or day of the week. The result is that I have Mr Sun and Mr May at the top of my list, rather than listed alphabetically. Sorting the list does not solve the problem. It switches Mr Sun and Mr May, but does not included them in the main list. Is there a setting I can use that will stop Excel 2007 thinking that these words are something that they are not?
I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.
I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).
1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance
This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?
