# Adding Columns With Manual Calculations To Dynamic Pivot Table

May 8, 2013

I have a problem as I need to present data with different data sources. I'd like the calculations to be as "automatic" as possible. The data consists of countries and regions and their sales pipe. The pivot table doesn't give me all the data that I need to present so I need to add extra columns with calculations and data from other data sources.

The problem I meet is that the 'total row' within each group needs to be reflected in my columns as well (F:G) It works as long as the data I've imported has the same amount of States within each group of Contry but if the number of states differs with the newly imported data - my "manual" columns are wrong.

(A;B;C;D;E;F;G;H)
Country;State; Red; Amber; Green; Quota; Total PV; Coverage;

I've created a Pivot table on the first 5 columns [Country; ...; Risk] (A:E)

The following columns are manually entered based on the pivot (F:H)

Quota (data source from another sheet)
Total PV (a sum of Red; Amber; Green in the Pivot)
Coverage (returns % of TotalPV/Quota)

For each group Quota and Total PV needs to summarize the above data within the group. I've 'hard coded it' today (SUM(F3:F5)) - F3:F5 can as well be F3:F8 or F3:F4 depending on the imported data. I have 8 different Countries (groups) with different amounts of States and 3 different sheets for each Region of countries so I need this summary to be automatic based on what group (Country) it belongs to. How can I make the calculation different so that it's dynamic as well as the Pivot table? Today it's not dynamic and it needs my 'hands on'.

Quota =IF(ISNUMBER(SEARCH("total";A6)); SUM(F3:F5); IF(ISNA(VLOOKUP(B6;Quota!B:F;3; FALSE));" ";
IF(VLOOKUP(B6;Quota!B:F;3; FALSE)=0; " ";(VLOOKUP(B6;Quota!B:F;3; FALSE))))).

## Add Columns With Calculations In A Pivot Table

Nov 2, 2012

I have two queries regarding pivot table.

I would like to include additional columns with formula in to a pivot table

I would like to get ride of the Items in a pivot table with Zero values without editing the source data.

I have attached a work file in to the below link, [URL]....

## Manual Sort In Pivot Table

Aug 16, 2009

I have a pivot table and if i select my headers i have the availability to sort a-z and z-a plus manual ...manual says to drag the item where i would like it... I have tried the excel database..they reference manual sort but really give no explanation.

Scenario.. I want to sort a-z.. then manual sort a few items to the top of the list..

## Pivot Table Manual Manipulation

Apr 27, 2007

My pivot table is not auto summing each column properly, and I can't manually enter a formula to do so. I tried adjusting the settings of the rows and columns through the pivot table wizard, but no luck. Is there a way around this limitation?

## Excel 2007 :: Unable To Set Pivot Table To Manual Update?

Aug 19, 2013

I am using Excel 2007 and my version of visual basic is 6.5.

I am baffled by the behavior of this code to manipulate one of my pivot tables. I am trying to set all but one of the pivot items in one of the pivot fields to not visible. Because there is a large number of items, I wish to suspend all automatic updates until all items are properly set to visible or non visible.

------------------------------------------------------
Sub SwitchBoards()
Dim BoardNew As String
Sheets("Board Parameters").Select
' Make sure we get the right value.

[Code].....

The MsgBox returns with: "Manual update is set to False" right after the instruction to set it to True!!

## Excel 2010 :: Pivot Table - Manual Edits Don't Revert On Refresh

Oct 22, 2012

If a user changes some data in a pivot table and then refreshes the table, the changed values are supposed to revert to the source data, right? This does not happen in my pivot table.

Any changes made in the underlying data get transferred to the pivot table refresh, as expected. But if I change some values in the pivot table, followed by a refresh, then the cells that I changed remain the same unless I happened to modify the corresponding values in the underlying source data too. In other words, the refresh operation does not touch the pivot table except specifically where an update is driven by a change in the underlying data.

This behavior is different from what i remember from, say, excel 97 / 2000.

## Pivot Table Arrangement But Without Calculations

Apr 25, 2014

I am trying to get my data to be visualized on a monthly basis. A Pivot Table works perfectly in getting the right arrangement, except that it performs a calculation in the values summary field. Is there a solution that allows the Pivot Table to plop the actual text values that it is pulling from? Maybe Excel Pivot Tables aren't the right solution, so maybe another type of software might be useful? Or perhaps a change in the VBA script? The original table looks like this:

ID Description Date Code Building
10223 Package/Split 02/01/2015 ALRM1 A
10223 Package/Split 02/01/2015 PLMB1 A
10992 Pump Test 03/13/2015 ALRM1 B
10992 Pump Test 03/13/2015 PLMB1 B
10992 Pump Test 03/13/2015 ALRM1 B
11432 Inspection 03/13/2015 ALRM1 C

The way I would like for it to look is this:

January February March
Building ID Description Building ID Description Building ID Description
A 10223 Package/Split B 10992 Pump Test
C 11432 Inspection

In the original report, there can be multiple codes for the same ID (ALRM1 and PLMB1 for 10223). In such cases, Pivot Table works by summing up the values, therefore displaying the ID in just one row. I would like for it to do the same thing (spit a multiple row ID as one row) but display the actual text values of Building, ID, and Description as shown. I don't need the code in the table, but it's there in the original data simply to differentiate between the two IDs.!

## Macros Effected By Manual Calculations On Run?

Jul 7, 2008

I always use manual calculation with Excel, does it affect the calculation within a Macro when I run it?

## Dynamic Pivot Table ....

Aug 28, 2008

I have a spreadsheet the includes data validation, auto fill fields using formulas and conditional formatting all over the place. The user will be adding rows of data whenever applicable, so I want to create a dynamic pivot table that will update automatically. I think I've done it properly, Insert - Name - Define. In the Refers To field, I've added:-

=OFFSET(Sheet1!\$A\$10,0,0,COUNTA(Sheet1!\$A:\$A),12)

I've then based the Pivot Table on the defined name. For some reason, when I add a new row of data, the pivot table doesn't update, I have to do it manually. When I go back into Sheet1 and click in the Refers To field, there is an extra "blank" row showing as included in the data range (screen print attached) Is my OFFSET wrong, or does it have anything to do with the fact each row (down to line 300) contains formulas and #N/A's?

## Adding Pivot Table With VBA

Jun 8, 2012

I recorded a macro to make a pivot table. It doesnt seem to work when i re run it... it is either...

Pivot table 4 (maybe its already been used... can i somehow say next avalibale name? ie pivot table 4, pivot table 5 , 6 , 7 and so on?)

or...

I dont have 5000 rows, but i don't know how to specify that is should only inculde rows were Col A has a value

Code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"damien test!R2C1:R5000C6", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable4", DefaultVersion _
:=xlPivotTableVersion12

## Pivot Table Dynamic Grouping

Sep 15, 2014

I have a table with outstanding days for Funds owed by multiple accounts which dynamically changes everyday I am looking to group this table by >5days, 6 to 14 days , 16 to 40 days ,>40 to 90days and >90days consistently even though the table will change dynamically every day..

## Macro For Dynamic Pivot Table

Jun 23, 2014

I am trying to make a dynamic pivot table using VBA. Unfortunately, Im not very good.I have a set of data with variable number of rows and variable number of columns and I need to make a pivot table. Need to select this data from sheet - Master, where the first data entry starts in cell A1. of the data in the sheet, i only need the name and age title In the pivot table fields -

ROWS needs to contain - name
COLUMN needs to contain - Age
Values need to contain - count of Age

After this is done, since the data will be dynamic, the pivot table will also be dynamic. i need to copy the pivot table data and paste it in another sheet so that I can do some filtering. The filtering part I think i can do my self.

## Dynamic Pivot Table Range

Nov 8, 2011

I've a macro that generates a pivot table from another excel workbook, however i need the range to be dynamic as the excel workbook im generating the pivot table from changes in number of rows on a weekly basis. Here is the existing coding:

Code:
Sheets("QA").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"QA!R1C1:R22580C12", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10

how to make the SourceData dynamic

## Pivot Table With Dynamic Range?

Mar 27, 2013

I have a sheet in my workbook called Results. This sheet contains a list of a fixed set of columns (A - N) but with a variable number of rows.

I am trying to create a macro to create a pivot table from this data, which automatically selects all the rows in the Results sheet to form the data set.

I've had a look at some of the other threads on this topic, but just can't get this working.

I have the following code that I have pieced together from other threads - you will see I am trying to set "PivotRange" as the data set and include this as the data source in the pivot. I get a subcript out of range error when I run this.

Dim PivotRange As Range
PivotRange = Range(Worksheets(Results).Cells(1, 1), Cells(lastRow, lastColumn))

[Code].....

## Pivot Table - Using A Dynamic Range

Jan 6, 2009

Because my pivot table is used by several users the data source changes often. In response, I created and applied a dynamic range using the offset formula. While the pivot talbe grabs the correct # of records, it inserts a blank column and row. There are NOT any blank cells in the data source range, as reflected by pivot table counts for the Blank column and Row. Any idea why Blank is showing up in my pivot table and how I can correct while maintaining the dynamic range?

## Dynamic Rows For Pivot Table

Nov 16, 2006

I wish to create a Pivot Table. It is based on source data with fixed columns (A to W) and dynamic rows. The following is my

Dim WS As Worksheet
Dim DataRange As Range

Set WS = ThisWorkbook.Worksheets("sheet1")
DataRange = Range("A1:W1", Selection.End(xlDown)).Select

tabledestination:=Sheets("sheet2").Range("A1"), tablename:="PivotTable1", defaultversion:=xlPivotTableVersion10

Dim PT As PivotTable
Set PT = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1")
PT.PivotFields("Type of Work").Orientation = xlPageField
PT.PivotFields("Profit Center").Orientation = xlRowField
PT.PivotFields("B/(W) CTD Net Rev").Orientation = xlDataField

Because I'm going to use it in my work, it must be reusable. And every time I use it, the numbers of row are different. Therefore, I made the source data into a dynamic range. The range changes when rows change. However, problem always appears in the following line:....................

## Use Dynamic Range In Pivot Table

Dec 21, 2007

Im trying for the first time to use a dynamic range as the 'source' for a pivot table. The pivot table and the source data are in two different tabs. The source data is on tab Log! and i have used Daves formula (i hope correctly) to define the dynamic range. The range starts at Column B and is 22 columns wide:

Refers to:=OFFSET(Log!\$B\$1,0,0,MATCH(""*"",Log!\$B:\$B,-1),22)

I have named this dynamic range DMR. I have a pivot table on another tab. When i try to use DMR as the source (Log!DMR), it gives me an error "Reference is Not Valid". I must have the syntax wrong somewhere.

## Adding New Field To Pivot Table

Jul 5, 2014

I have two fields in pivot table, "HATA TOPLAMI" and "SIRA NO". "Hata toplamı" is sum of items, and "Sıra no" is count of items. İ want to divide these two fields and add it to the pivot table, like "HATA TOPLAMI" / "SIRA NO". I have tried calculated field function from pivot table. İ wrote "="HATA TOPLAMI" / "SIRA NO"". But the summary is false. I want to divide Sum of "Hata toplamı" and count of "Sıra no" but instead it divides with sum of "sıra no". Is there a way to do this?

## How To Create Dynamic Pivot Table Title

Jun 7, 2014

I have a pivot table without report filter and I set insert page break after one of my selected row labels.

I also would like when I print, these row Labels become the title of these pages at the same time.

Actual File : Untitled.png

However I would like when I click on print or save as pdf will print all pages as one file and each Center that I have set page break for, in separate page and add that specific center as its title:

Attachments for more details:

test_Page_1.jpg
test_Page_2.jpg
test_Page_3.jpg
test_Page_4.jpg

## VBA To Find And Select Last Row In Dynamic Pivot Table?

Jan 31, 2014

I am having issues with a VBA script. The Spreadsheet is linked to a SQL DB and updated with data from scripts that are executing throughout the day. This is then fed to a dashboard. We have a Baseline that shows in the Date column as 0/0 and 1/1. I need a way to ensure that the 1/1 is always in the last row of the chart so the baseline shows across the entire range of dates. The current method is to declare a variable and add 1 to it whenever we iterate through the sort for the other dates. However everyday the 1/1 baseline column ends up further to the beginning until it is next to the 0/0 in position 2.

So I am trying to find a way to have the macros find the last row of the Date field and then set the position to be equal to the last row.

## Pivot Table - Dynamic Source Data

Sep 26, 2011

I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.

These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?

## Manually Adding Formulas To Pivot Table

May 22, 2014

I've created a pivot table as shown in the attached image - I've had to hide most of the data but I'm not sure how confidential it is so thought I should play it safe.

excelforum.JPG

In between the Job Board Applications column and the Registrations column I want to have a Cost Per Application column.

In this fake example, the company that I've called 'Excel Forum' (imaginative I know) pay \$1000 per month for their email marketing, so the cost per application from email would be 1000/1150 = \$0.87.

Is there a way to insert a column in a pivot table that you can manually add formulas to?

I've tried replicating the pivot table underneath using cell=B6 etc. and the GETPIVOTDATA function then hiding the whole pivot table apart from the filters, but because there are a different number of subcategories every month the cell references don't work when the filter is changed.

I also tried to add a calculated field but this didn't seem to be what I wanted.

As a last resort I can add the cost per application manually to the raw data and include it as a column when making the pivot table, but this would be an ENORMOUS job to do every month so I'm in need of a better solution!

## Adding Pivot Table Field With RANK

Dec 17, 2008

I have a PT with multiple data fields that I want to Rank individually and then give those ranks a weight percentage. Once I have that weighted rank, then I can sort PT items based on the rank.

Is there a way to do this inside the PT?

I.E.

Items are PNs

Data fields are NUMBER OF TRANSACTIONS, QTY SOLD, DOLLARS SOLD

I want to rank each field and then assign a weight to it. Then I can sort PT based on the RANK data field.

## Pivot Table - Adding Data And Refreshing

Oct 17, 2011

Its been ages since I used a pivot table and I cant remember much.

I did one last week - lets pretend on the range A1, B100.

Ive since added some extra info into the range and it now goes from A1, B110.

I go to my pivot table and hit "!" and it updates. How can I be certain its included everything from my range? Is there a way I can check the pivot table to see where the range is from?

## Adding Calculated Fields To Pivot Table

Jun 7, 2013

I have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.

Day
Count of Posts
Sum of Impressions
Impressions Per Post

Monday
52
1,881,468

[Code] ........

## Adding A Pivot Table Field With RANK

Dec 17, 2008

I have a PT with multiple data fields that I want to Rank individually and then give those ranks a weight percentage. Once I have that weighted rank, then I can sort PT items based on the rank .....

## Calculations To Manual Before My Code Runs And Set It Back To Auto When My Code Is Done Running

May 12, 2009

I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?

## Excel 2003 :: Dynamic Range For Pivot Table

Jan 21, 2013

How do I create a Named Range that is dynamic (I think using the OFFSET function) that automatically expands and contracts as the rows and columns of my data changes?

Next step, I then want to use that Named Range as the source of the data for a PivotTable.

Yes, I know starting in Excel 2007 you can use Tables and don't need to create a dynamic named range. That is not an option at this time.

## Pivot Table Dynamic Range, Last Used Cell Not Same Column

Oct 26, 2009

I've got two pivot table reports working off one dataset.

I've named the range Recharge with the formula as below..

=OFFSET('Recharge'!\$A\$1,0,0,COUNTA('ABC Recharge'!\$A:\$A),16)

But this uses column A as the longest column... but sometimes it will be column I - how can the formula be adapted ? or can it be ? i've been looking at the Max function and trying to incorporate that but my limited brainpower has gone to mush.

## Dynamic Refresh Pivot Table Pagefields With Macro VBA

Jul 8, 2006

A simple example of the problem: Imagine a pivot table has two pagefields at the top. The first list the gender ("boy" or "girl"). The second one below it lists people's names. How can it be programmed so that when the first (gender) filter is selected to "boy", pagefield 2 (the names) will only display the names of boys. So I would like the pagefield to work like regular multiple filters would. (And not list every possible item for all)