Pivot Tables And Showdetail/drill Down

Nov 14, 2007

it possible to get a drill down of multiple cells from a pivot table into the same sheet easily, as when I use

Range("BE23:BE26").Select
Selection.ShowDetail = True

it only pulls out the first drill down (i.e. the equivalent of double clicking BE23)

I'm sure there's a work around I can write which performs each drill down separately then combine the sheets, but it's probably going to end up fairly longwinded!

View 9 Replies


ADVERTISEMENT

Deleting Drill-down Sheets With Pivot Tables

Jul 11, 2006

I've just set up a workbook full of pivot tables linked to an access database. The book will be used by several individuals interested in both the statistics presented in the tables and in the details underlying them. My concern is the accumulation of the extraneous sheets generated by drilling down to details. Is there any code I could use that would delete these sheets as soon as the user leaves them?

View 4 Replies View Related

Pivot Table Drill Downs To Appear In New Worksheet

Aug 4, 2009

When you double click on data in a pivot table this brings up the drill down info. Is it possible for this to appear in a new workbook rather than the active workbook?

Any help if grately appreciated. If you need more information please post what you require and i will reply.

View 9 Replies View Related

Format Pivot Table Drill-Down Results

Sep 8, 2006

Is there any way to change the format for pivot table sub data? Or maybe use a marco for it. The data table that appears when you double click any field on the pivot table range.

View 6 Replies View Related

Drill-down Pivot Table Results In New File

May 15, 2007

I want Pivot Table Drill-down result in New excel file. when I have a pivot table, When I double click any data cell, the result comes in the new sheet(Drill-Down) of the same file BUT I want the same result to come in a New Book (a new excel sheet) and not in the same file.

View 3 Replies View Related

Validate Pivot Table Drill Down Data

Dec 10, 2007

I have a question that is bugging for quite some time, see if you know. Is it possible to insert a data validation drop-down menu to a pivot tables drill down data automatically?

I really do believe that pivot tables should have this option, it would make data validation much more easier. I going to suggest that to the guys developing Office 14, according to them the new Office will more Business roled based. Lets wait and see a !

View 5 Replies View Related

Refine Pivot Table Source Data On Drill Down

Oct 26, 2007

Attached is a file which shows the problem. I bascially have two headings. The first heading contains the letter "b" 9 times but only has values against two "b"s. When this is entered in a pivot table and the value is drilled down (in this case 3, highlighted in yellow), I only want the numbers attached to the two "b"s to be shown rather than all the 9 b's (thereby omitted all the zeros).

View 3 Replies View Related

Adjust Column Label Selection Multiple Pivot Tables Based On One Pivot Table

Aug 16, 2013

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.

View 3 Replies View Related

Change Pivot Source Data In Multiple Pivot Tables?

Jan 21, 2013

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.

View 4 Replies View Related

Refresh Pivot Tables Linked To Pivot Table

Jul 25, 2006

I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these

Sub Macro1()

ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"

End Sub

View 6 Replies View Related

Pivot Table Based Off Multiple Pivot Tables

Sep 5, 2006

Is it possible to create pivot table from another multiple pivot table.

Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table

View 3 Replies View Related

Can Pivot Table Be Created From Several Other Pivot Tables

Mar 5, 2014

I have 12 months worth of data in twelve separate workbooks each with a pivot table, I have copy/moved the pivot table tab from each into one workbook so now I have a workbook that consists of 12 tabs each with a pivot table. What I would like to be able to do is create a summary table with the full years data; where I am running into problems is that each months table has slightly different row and column counts and labels making any formula like =sum([sheet 1 cell a1]+[sheet 2 cell a1]) problematic.

View 3 Replies View Related

Pivot Tables: Pivot Table Layout

Oct 14, 2003

if there is a way to display a table as column percentages but have the totals as raw numbers.

View 9 Replies View Related

Show All Pivot Items In Pivot Tables

Jun 19, 2008

I've got 4 pivot tables (all derived from the same base data) on 4 separate worksheets. I've been able to (with this help of this site) to use VBA to hide pivot items on all of these sheets using a list on a user form. Hide/Show Pivot Table Field Items. Hide Pivot Table Fields Pivot Items by Criteria

I now need to be able to show all the pivot items on only 3 of the 4 pivot tables, with the 4th pivot table being left untouched. For ease assume that my sheets are sheet1, sheet2, sheet3, and sheet4. The tables I wish to update are on sheet2, sheet3 and sheet4. The pivot table on each sheet is called "PivotTable4" and the pivot item is called "Business". The pivot item contains 12 business names (Business1, Business2 etc etc)

Is there an easy way of doing this? I've spent the day looking through the internet and various "Dummies" books but with little success, I fear that I'm obviously below even Dummy level

View 5 Replies View Related

Pivot Tables With Graphs

Mar 13, 2014

I have a 8 pivottables with a graph for each. is there a way that i can have a dropdown menu to select which graph to display instead of having all these graphs everywhere....

View 4 Replies View Related

Printing Pivot Tables

Mar 21, 2014

I have an issue with printing a worksheet with a pivot table. When refreshing the table, depending on the filter choices, the table length with expand and contract. When the pivot table contracts, it leaves a light blue shaded area. If you try to print the worksheet it includes the blue section. Is there a print macro that can be written that will only print the sections with values or perhaps a print setting that would exclude the shaded area?

View 1 Replies View Related

Pivot Tables Do Not Display?

Oct 27, 2011

When in a worksheet and clicking in the pivot table results, the pivot tables no longer display.

I did have a reinstall of Office last week but can't see how that would have impacted this. Otherwise, I only use the pivot table command to 'refresh all'.

I know very little about them and didn't create this workbook.

I do add entries to the source data and have tried to change source data but I get Reference is not valid.

View 1 Replies View Related

Row Labels In Pivot Tables?

Feb 14, 2012

When I run pivot tables, the row label descriptions are only displayed at the highest level for each category. As displayed below the highest level on the row label is MOB and is only detailed in the first row. I need for each of the different row label levels to display the data so there are no blank cells.

MOBMan. Boxset 2Large1328Manual StickeringN/A500Shrwp 13+Large16680Small596Shrwrp 7-12Large35704Small2411SW 1-6Large87912Small14635SW w/ PremLarge26966OUTDisplay Lvl 1Small100Klutz Standard Display 6-packN/A2499Man. Boxset 1Large40000Man.

[Code] ........

View 2 Replies View Related

Pivot Tables Within Excel?

May 15, 2014

All I want to do is write some vba script that inserts a pivot table based on all of the data in my current workbook.

Therefore I want something incredibly simple if it exists like:

ActiveWorkbook.PivotCaches.Create(SourceData:= ActiveWorksheet.Select.AllData)

ie the vba code that just takes the data you've got in your current worksheet and creates a pivot table.

View 1 Replies View Related

Formatting Pivot Tables With VBA

May 27, 2014

I have four sheets with a lot of pivot tables on each and my problem is that whenever I hit Refresh the formatting of the pivot tables is always changing. It seems that this is something of an issue in Excel 2010 and that they only way to truly get around it is to re-format the pivot tables upon refresh with VBA code.

Any resource or thread I can use to learn the commands to format the pivot tables?

I am getting better with VBA code but seems to always get stuck on trying new things because I do not know the commands.

View 1 Replies View Related

Two Pivot Tables With Combo Box

Mar 23, 2007

I'm trying to sync a common field in two different pivot tables.

I have 2 pivot tables on a single worksheet.
Each table has its own data source - 2 different databases (had to set it up that way to present all the data requested).

The 2 data sources have one column of data in common. This column is called Projects and all project names are the same in both databases.

BUT...

Pivot Table 1: Projects is in the ROW area (multi-select dropdown)

Pivot Table 2: Projects is in the PAGE area (single select drop-down)

I would like to link the Projects data items so that when I select a project name in the Combo box, the same project name would seamlessly be selected in both tables.

Each table would populate with its own data based on the project selected.

Basically, I'd like to use the method illustrated in this Flash file:
[url]

This method would have worked beautifully if it weren't for this reason:

Table 1: Project data is in the PAGE field
(single selection)

Table 2: Project data is in the ROW field (multi-selection)

If the Projects data was in the PAGE field in both tables, my code would look like this:

ActiveSheet.PivotTables("Table 1").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value

ActiveSheet.PivotTables("Table 2").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value

But no, because the ROW field is a multi-select one, I get this kind of

View 9 Replies View Related

Relink Pivot Tables, VBA

Dec 14, 2007

I have several pivot tables that need to be re-linked to an Access database (really change the link to a different month's data). Currently, I do this manually, but I was hoping to write a macro to do this. Data is saved by month so I can't just create a "current" file as to not change the links each time. I can only get code to work for the refresh portion.

View 9 Replies View Related

VBA - Pivot Tables - Various Workbooks

Jun 5, 2009

I have used the macro recorder to create a macro that creates a pivot table from data located on another workbook.

with this said i have two questions.

How can i make it so that the workbook containing the data for the pivot tabel does not need to be open?

the second question is why am i gettign stuck with a macro that sticks right here:

View 9 Replies View Related

Pivot Tables: VBA Refreshing

Oct 16, 2003

Is there a code I can use to update pivot tables e.g every 10 mins?

View 9 Replies View Related

Working With Pivot Tables Using VBA

May 2, 2006

I am working with VBA to create a pivot table, and have done just fine so far. However, I need to create a two buttons that will run the following macros: 1. A macro that will remove the selected header (either row or column) from the pivot table.

2. A macro that will put the removed header back into the pivot table.

I need to be able to click on the header (whether its the row or column header) and then press the button to remove it from the table. The second button should then add that header back into the table. My code for creating the table is fine, I just need to work out the buttons. Below is what I have so far. I was think that if I had variable for the header name it would be able to tell which header to remove (so I used Set iField = ActiveCell.Value), I also tried ActiveCell.Text. All I need to do is put the text in the selected field into the PivotFields range to make it hidden. However, I keep getting an error (Compile Error: Object Required) on the line Set iField = ActiveCell.Value. Below is my current code.

Sub PivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Pivot Table Data'!R1C1:R1892C7").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Product", _
ColumnFields:="Location"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub..................................

View 4 Replies View Related

Calculations Between Two Pivot Tables

Aug 3, 2006

I wonder if it is possible to make calculations between two pivot tables which have the same number of columns and rows and put the outcomes in a third pivot. For example Pivot 1 contains revenue data, Pivot 2 contains cost data and Pivot 3 provides the calculated outcome of revenues minus costs.

View 7 Replies View Related

Compare Pivot Tables

Aug 9, 2007

Is it possible for me to compare two pivot tables and get a report. in the attached example, is it possible to compare sheet1 and sheet 2, if the values haven't changed, then it should be green, or it should be red.

View 3 Replies View Related

Sumifs Vs Pivot Tables

Feb 14, 2008

I read in your "best Practice..." sticked above that it's better to use Pivot tables then multiplied criteria SUMPRODUCTs (or array formulas). i agreed with it because i found it took long time to recalculate the sheet. do you suggest it refering the new SUMIFS formula?. i found that a SUMIFS that refers to Tables can be replacement for GETPIVOTDATA. In my sheet there are several dozens of GETPIVOTDATA.

View 4 Replies View Related

Pivot Tables Design With 5 Levels

Jun 24, 2014

How can I format a Pivot Table with 5 levels. I try to use the "Design" but it only format the first 2 levels of the Pivot Table. I need to distinguish each level with different colors. I think manually is a bit difficult.

View 6 Replies View Related

Pivot Tables Using Dynamic Ranges

Jan 7, 2014

Worksheet A - I've set up a dynamic range to display the last 12 row entries of 2 columns
Worksheet B - pivot table of the range

The dynamic range is working perfectly, it displays the correct rows I can set up the data source once... the results on the pivot table display corectly

Problem When i press refresh, the data source "dissapears" and i have to rebuild the pivot table.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved