Free Microsoft Excel 2013 Quick Reference

Linking static notes with pivot table

I have a pivot table which draws data from a dynamic worksheet.
I want to include some notes in the static columns next to the pivot table; however, I want the notes to be associated with a record of the pivot table. i.e as the pivot table grows downwards, the notes should also follow the record they were originally posted beside.
How do I do that?

Post your answer or comment

comments powered by Disqus
With Excel 2007 is it possible to have a live link to the data with Pivot tables if the data is in another sheet in the same book? If this isn't possible then what is the best way to have the pivot reflect the data is gets in real-time or closest to real-time?

Why is it when I copy sheets with pivot tables that are linked to other
worksheets that data gets lost? Is there any way to avoid this problem? I
have just spent hours doing a paste special of the values in the pivot table
rows. Any suggestions?


I'm trying to build an Excel tool for users to check material spend/costs throughout the year, based on data "pulled" from a central database. I've managed to create the query (using MSQuery) to get the raw data, and added a worksheet to do the various calculations I need. I'm struggling however to show the data in a Monthly format in a Pivot Table. The only date I can use is in dd/mm/yyyy hh:mm:ss format. I know how to customise the format to show it as Jan 2012 for example, but when I try and pivot the info it is still showing an individual record for each date, rather than grouping by the Month/Year.

Is it possible to do something like this with Pivot Tables? TBH, I'm not a huge fan of them so very rarely use them, I mainly use Access for reports/queries etc, but the users are more used to Excel. I would try and export the data from Access to Excel but we are all remote/home users and the network link isn't the greatest, so the fewer steps I can use the better.

Hi all, I have an issue with pivot-tables:

My pivot-table is sourced from a range with the following column headers and data:

Name Firstname Middlename Forename

Tom J Jones Tom J Jones
Eric P Jones Eric P Jones
Keith A Daves Keith A Davies

The data comes from an ODBC link to an Accesss query.

If I click Forename on the pivot-table and select Jones, I would only want to see the available sub selections of Middlename and Firstname (J, P and Tom, Eric in this example), rather than the full list including A and Keith at present.

Is there a way of doing this thru pivot-tables?
Amy xx


I have been noticing since I got migrated to Office2000 that my excel
workbooks with pivot tables frequenty (and unexplainably) will prompt
me to "save changes" when I have just opened them to look at data and
try to close the file again.

NOTE: The workbooks I'm referring to do NOT have any programmed macros
or VBA code.

Here are my observations:

- workbooks with embedded pivot tables are more likely to exhibit the
"phantom save as" behavior that workbooks without pivot tables.

- the behavior usually occurs when you open the workbook and activate
any cell within its pivot table. It appears that excel thinks you
clicked "refresh table".

- by answering "yes" to the "save as" prompt, the behavior (at least
temporarily) goes away. (next time you open it, and close without
changing anything, you don't get asked to "save as")

Anybody else notice this happening?


I have a worksheet that has about 20 tabs, with each linking to a massive pivot table I have displaying data I change every month. When I update the massive table, each tab's pivot table changes. My question is:

How could I calculate a 12 month and 10 year rolling average as I enter new data each month. For example, if I input March 2012 data, I want it to show Apr 2011 to March 2012 average. Next month, it will automatically change to May 2011 to Apr 2012. Same with a 10 year average.

The tricky part is that if it was all in a 2 column format, it would be easy. But, since my pivot tables are sorted by year -> group -> category, with all 12 months on top, OFFSET will not work (I assume).

Please help. I have attached a printscreen to show what I mean. The values go back to 2000, but they are nbot showing on the printscreen.

Excel Example.jpg


EDIT: I forgot to add that using the "distribution date" as a marker would not work as some of my data does not have a distribution that month, therefore the date does not get entered.

Yeah, sorry this isn't really a joke. But you still might smile, chuckle or whatever.

--I have re-read over Dave's information about spreadsheet designs so many times. And, I admit, it was like trying to understand Worf in his native language in Star Trek. I know some people did learn the basics of that language, but I couldn't. It was the same way with Pivot tables. I did use it, but in a very small way. I had seen a large number of responses talking about it. It has always been stuck in another language to me.

--Then I will say that somehow Norie spoke my language, it took me a minute, cuz I understood it, but couldn't realize how. This particular thread from two years ago, is what opened my eyes or woke me up, whichever I was in. I do know that I have seen many, many things that kept saying do this and do that. But it never sunk in.

--I have been messing with Excel for a few years, but could never get the hang of it, all I was doing was piecing pieces together and somehow I look like the guru in my company for Excel. I do know what my goal is for the Excel projects, and I can reach them. But I always had felt that I was like 15 years behind everyone. Now it feels like I am about 7 years behind, which is a relief.

I applaud and thank everyone here for doing what they do, it is a wonderful community and I am happy to have found this when I did.

geez, I'm going to end this now before I get carried away. LOL

parameter query with pivot table

can i make a parameter query that references a pivot table...?


I have a custom graph which has both stacked columns bar and a line graph
with pivot table. Everytime I refresh data, the graph switch to graph with
colums bars.
How can I lock a graph to prevent switching?
Thanks a lot, R.

With Excel 2007 if I have a pivot chart set up using an olap data source with
4 Values (measures) which are the row values and each being graphed as a
separate line in a line graph - lets just say they are A, B, C, D.

If those items are listed in the Values box on the bottom right of the pivot
table fields list window as C, A, B, D when I hover over the legend for any
of those in the actual graph it will display the wrong one on the hover over.
If I hover over the first item in the legend "A" it will show me the hover
over details for the first item in the values box "C".

Not only that , but even worse it was displaying incorrect values in the
graph. If I resorted the values inside the "Values" box in the bottom right
of the Pivottable field list window it seemed to correct them, but it all
seems to be acting up.

I have a screen shot I could take of this if it will capture the hover over
and I could try to recreate the wrong values.

Has anyone seen this before? I have extensive experience with pivot
tables/olap and have never seen anything like this in prior versions.

I have a pivot table, that when broken up (Show Report Filter Pages) creates about 150 separate worksheets. I am looking for a macro to copy and paste the values of all of them, (they created 150 sheets with pivot tables, which I don't want) so I do not need to do it manually.

I did see a suggestion here: , but when I run that I get an Run-Time error 1004 (can't move part of a Pivot Table Report)

(Excel 2010)


Is there a problem with using macros with pivot tables? I tried to create a pivot table using macros, but for the data part when I used "Average of Sales", it came up as "Sum of Sales" when I ran the macro.

Another issue is that my data could change from time to time in terms of the number of records in the sheet. I thought I could take care of that my using CTRL+A. However, this does not seem to be working. It is only reading the original number of records that I used in creating the macro.

Please help


We are maintaining a Report for individual users with details of work done
by them during the day. The Report contains details such as:
1) Date
2) Name
3) Client
4) Version of Work Done

Now, we need an automated Consolidated Sheet with Sum of versions done for
each Client by each Person on a specific Date.

I tried using the Pivot Table... I am not well versed with Pivot Tables. Can
we make a Pivot Table by selection the specific ranges from multiple
(individual) Sheets so that we can get this data...

Please help me with this.


I have a small problem with pivot tables I want to group data per tags. For example I have a list showing the cities and the countries they are located. I want to see the grouping in different columns; please refer to attachement for further details.

Sorting does not work my special purpose. I am trying to create a dynamic table.

Any explanation on how you construct the pivot table will be much appreciated.

This is building upon an existing thread; 'inquire split data base with pivot table'

I'm trying to replicate those suggestions but coming across a couple different problems. Each month I enter a new data sheet to track downtime; that data always has the same headings and is pulled using a query from a server at our site. From that I'm making the pivot table in a seperate file to consolodate everything using MSQuery:

<data><pivot table>....Check: External data...Click [Next]
Click [Get Data]....Database: Excel files
(Browse to the file...Click [OK])

Then I've gone into the query and edited the SQL code to look like:

SELECT Year, Week, Downtime
FROM `C:Pivot table`.`NOV-07$` `tbl`
SELECT Year, Week, Downtime
FROM `C:Pivot table`.`DEC-07$` `tbl`
SELECT Year, Week, Downtime
FROM `C:Pivot table`.`JAN-08$` `tbl`

And that seems to give me a pivot table that looks familiar; the trouble is when I try to set the table to show a 'sum' instead of 'count' it shows nothing but zeros.

Another problem is the pivot table is in a seperate file from the downtime query; when I move the sheet holding the pivot table back to the original file I'm not able to get back into the SQL option. I go into the wizard and get an error message when I click on the 'get data' button.

Hopefully this is making sense - thanks for any help!

I searched and found similar questions but not quite what I'm looking for. (Doesn't mean it isn't here somewhere.) Anyway.... I'm trying to make a 'canned' pivot table linked to a csv file. The csv file always has the same columns but a variable number of rows. What I tried to do was first open the csv file and then open my pivottable.xls file. In the xls file I copy the csv file over using if(condition,true,false) to recreate the flat table in the xls file. Then I make the pivot table. Then I save the pivottable.xls and close excel. Before I open either file I update the csv file. Then operationally I have a batch file that first loads the csv file and then loads the xls file. When I up examine the xls file all the rows are transferred as expected but the pivot table contains the original results. I would be willing to have my users manually update (F9?) if that all it took but that doesn't seem to work either. I suppose I could do this with a Macro but I'd prefer not to since it seems that it should be doable using just links. Any suggestions? -thx-

Please could someone help me with this? I'm ok with excel and vba but don't have experience with pivot tables.

I have several pivot tables, (controlled by some vba), and I need to have their axes changed to be static. Currently they are dynamic and are driven by the data that is generated. Using the x axis as an example: if there is no data with the value '5%->10%' in the source data, then there will be no column for '5%-> 10%' in the pivot table.
Instead of this, I want the '5%-> 10%' column to be there, and just be empty.

I hope that's clear. (In the source data, the 5%->10% values come from a lookup table. So i already have a fixed and definitive list of values that can be used to generate these fixed axes.

So the upshot of all this is that the pivot table will always have exactly the same size.

Thanks very much

I'm relatively new to using Pivot tables. I have the following code set up right now:

=GETPIVOTDATA("Average of OFF",Pivots!A2,"H/R","R","W/L","L")

It works fine. However, I want to use indirect, so that I can make the "A2" a link instead of hardcoded (so I can get this data from diff pivot tables.

I coded it using INDIRECT so it would use the Pivot table From Column A and the Row listed in "C1"; however, with all the Quotation Marks, it's getting confused. Any help?

=INDIRECT( "GETPIVOTDATA("Average of OFF",Pivots!A" & C1 & ", "H/R", "R", "W/L", "L")")

^doesn't work, obviously.

I'm dealing with pivot tables with quite hefty amounts of data, (32,000+ lines). Occasionally after reopening the workbook the data in the pivot has gone static and can no longer be modified. You can no longer select the field table list as it no longer recognises the pivot. It's as if the table has been copied>paste values.

Any thoughts?
I'm using Office 2007, though saving as .xls

Hi there!

Wondering if anyone can help me with this problem. I am fairly new to macros and haven't even come across pivot tables until now, so any help would be appreciated here.

What I have is a spreadsheet which links into an OLAP cube to run filtered reports, there is a date drop down list which is the main filter, this goes like this...

Time By Week

This is filtered usually by week (we don't drill down as low as day, but the option is there should anyone need it. Now, the problem is, that every week the managers link to this **** via the intranet to see the latest figures, and someone has the unthankful task of opening 100 spreadsheets, advancing to the next week, then resaving the sheet, to save the managers some time! Now, I have worked out a VBS script which can open all these sheets, run a macro and then close the sheets, and I can schedule this script weekly, what I don't know is how to , in VBA, change the time by week drop down list to the next week. I have tried recording a macro and manually changing it, but this just shows me how to set it to a known value. Is there a way i can dynamically change it to just the next record? For what its worth, here is what I get when I manually change it to the next record in Macro Recorder....

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Time By Week]"). _
        CurrentPageName = "[Time By Week].[All Time By Week].[2005].[Week 6]"
Anyone able to help? Thanks!!!!!!

I have two pivot tables, they use same source of data so i make pivot table B
link with pivot table A, not only the save size but also they can be
refreshed together. It usually works well but today don't know why pivot
table B becomes a "mirror" of pivot table A: When i change one the another
one changes in the same way, so I cannot make them to analyse with different
content, why?

Did I setup something by accident or what?

I am trying to extract data from linked databases spreadsheets using Pivot
My colleagues and I have constructed databases in Excel 2003 containing more
than 300 fields (which is more than Excel 2003 can handle). To accommodate
this we have split the databases into several worksheets and linked them via
a column of reference numbers. The columns are multiple time based
categories (times 24), and the records (rows) are based on dates. The bulk
of the data is mainly text (from dependent dropdown boxes) with some
numerical data.
We have extracted the data using SumProduct or Autofilter, because
previously we had been unable to get PivotTable to give us meaningful
Recently, as a result of browsing some posts in this forum, and their links,
I have been able to get promising results with PivotTables based on the
reference number column.
My question is this: How do I substitute dates for the reference numbers?
The dates are in a column adjacent to the reference number column in a
different worksheet of the same database.
I am using the “Multiple consolidation ranges” with the PivotTable wizard.
I am hoping someone is able to point me in the right direction.
Thank you.

Excel 2003
Since there is no way to refresh pivot tables in a shared workbook, i have
created a second (not shared) workbook, identical to the shared one.
It contains links to all the cells of the shared files and pivot tables
summarising the shared (linked) data.
Later I would like to link the pivot tables of the unshared file back to a
little summary table in the shared file.

My Problem:
The pivot tables of the unshared file should summarise the linked data,
since every cell contains a link (formula), empty cells are counted by the
pivot tables .

I've tried to use If functions to set empty cells empty (if(xy 0, xy, "")
but the pivot table still counts those cells.
Also, setting the pivot table count to countnum retrieves wrong values,
since some of the cess contain figures and some of them contain text.
Somtimes couloums dont contain empty cells at all.

any idea how to solve the problem? I guess I'm looking for an IF function
gives out a value, that is not counted by pivot tables.



I need to have a series of custom tables, specifically formatted in MS

I decided I'll make them based on Pivot Table as a source data and
Excel links with GETPIVOTDATA do deliver the data to the proper MS
Excel format worksheet template. After filling with dynamic data from
pivots the template is saved as workbook needed.

Each table consist of around 100 of GETPIVOTDATA links. Each link has
it own pivot table in a 'working' worksheet. All the pivot tables are
based on the same external MS Excel file economizing on resources.
While making the pivot table I decided that the external data is
returned to Excel file.

I use VBA to manage thos Pivot tables. As a result the pages of the
pivot tables change delivering different results in Pivot table.

Unfortunately I have faced two difficult situations:

1) Data that I use seems to remember with the quite 'statically'. Yes,
its true that when I update the MS Excel source file and give refresh
on the pivot table, the pivot refreshes. But if I copy the folder both
with source and report files the new, copied report file will still
source from the original source file. To change it, today I have to
manually change the source for each pivot table that is quite
time-consuming. Unfortunately I do not know how to make those links
more dynamic (always for example take from source.xls in the same
folder) or update the source automatically with VBA.

I'd like to make it automatically as manual work always causes more
unexpected errors and its quite unefficient. Do you know how to solve

2) When I change the pivot with VBA macro, the pivot itself changes,
but the links in custom formatted tables on other worksheet do not
'refresh'. I made a workaround by preparing special VBA procedure that
after VBA pivot table update, overwrites the links with identical
formulas in all cells. (Similar result to selecting the cell manually,
selecting the formula and pressing Enter). After that the links are
'refreshed' with the proper, chagned data.

This issue is done automatically, but maybe some of you know more
efficient / elegant solution?

My office is 2003 Professional, my system is WinXP.



mbobro's Profile:
View this thread:

No luck finding an answer? You could always try Google.