How to reference cells in a Pivot Table using Field values?

I have a pivot table created from 3 fields where (for example):

field1 values are: "1", "3", "5"
field2 values are: "A", "B", "C"
field3 values are: "X", "Y", "Z"

So given a pivot table PT1 I want to reference a cell within the table in a similar way to PT1["1"]["B"]["Y"] and get a Range item returned. Is this possible? Thanks.

EDIT: codes:

Public Sub ColorIt2() Dim rng As Range For Each t_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F1").PivotItems For Each r_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F2").PivotItems For Each h_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F3").PivotItems For Each b_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F4").PivotItems If t_item.RecordCount <> 0 Or _ r_item.RecordCount <> 0 Or _ h_item.RecordCount <> 0 Or _ b_item.RecordCount <> 0 Then Set rng = ActiveSheet.PivotTables("PivotTable1").GetPivotData(t_item, r_item, h_item, b_item) rng.Select Selection.Interior.ColorIndex = 40 Selection.Interior.Pattern = xlSolid End If End If Next b_item Next h_item Next r_item Next t_item End Sub

-------------Problems Reply------------

Not sure, but maybe you want the GetPivotData function?

Category:excel Views:1 Time:2010-08-31

Related post

  • Excel 2013 - pivot table calculated fields - referring to other dimensions in calc 2014-08-15

    I'm struggling to find much useful documentation on Excel 2013 pivot table calculated fields. My pivot table is fairly simple. I have a 'Company' dimensions as rows (e.g. 'A', 'B' etc) and calendar weeks as columns ('1' --> '52'). The value in the

  • Referencing cells in a pivot table. 2012-12-20

    I want to reference a cell in a pivot table. Lets say it is cell C4. rather than seeing "=c4" in the target cell, a function called "getpivotdata" appears. I don't want that, I just want to see the cell name. --------------Solutions------------- Hi,

  • Can a pivot table calculated field's formula reference the row value of another field in the same pivot table? 2014-04-18

    I would like to be able to use the following formula in a pivot table calculated field: =IF('Cost Type' = "Internal Labor Cost", 'Jan Delta' / 78, 0) However, the result is always zero. Is there any way to reference the row value of another pivot tab

  • Creating a calculated cell in a Pivot Table 2012-06-18

    Can a clculated field be created by using two of the Pivot Table result fields in Excel 2007? I tried creating a calculated field by using the qty field and the unit cost field from the pivot table but all I ever was able to get was a 0 for the resul

  • How to use a VBA custom function in a pivot table calculated field? (Excel 2010) 2015-03-05

    Although I suspect the answer is that I can't. :-( I'd like to use a custom function to calculate a score based on two other fields in my data. The two fields might be, for instance, an identifier for a question (Q1, Q2, Q3, ...) and another for the

  • How can I activate the pivot table list field with the keyboard? 2012-07-24

    Hi!! Does anybody know which is the shortcut for activating the pivot table list field to check or uncheck fields with space bar. Thanks for your time --------------Solutions------------- To view the field list you need to click on any of the cell (d

  • Pivot table Group Field ignores start date if before start of data 2013-04-12

    I have a Pivot table and am using "group field" to group dates in the table rows into weeks. The data set begins at the beginning of a month but part way through a week. Setting the Group Field to the Monday before and counting 7 days from there does

  • Error 1004: invalid references when creating a pivot table with vba 2010-08-31

    i've in my excel file a sheet with 3 columns (id,type,month). knowing that many items appearing more than once, i need to get the number of occurances of each id per month then to get the number of ids appearing twice, appearing three times, ... i wa

  • Get Pivot Data: changing reference cell in get pivot data formula 2012-01-01

    I am creating an overview in another tab in excel which all the data comes from several pivot tables. I have created a get pivot data formula of which is =GETPIVOTDATA(" CCE Cases";'P01'!$A$7;"Vendor";$B4;"Plnt";$A4). I copied and paste the formula i

  • Excel pivot table calculated field total returns unexpected value 2009-11-16

    I have an Excel spreadsheet which has a pivot table on it. The data itself is quite simple, Number of Units and a Discount (percentage from 0 to 100), pivoted on date across the top and customer down the left hand side. Those numbers are straight fro

  • #VALUE! error in a Pivot Table Calc Field 2013-02-12

    Hi I am using IFERROR in a Calc Filed on a Pivot table. However I get #VALUE! errors The formula works ok on a spread sheet, it is just when I add it to the Calc Field that the error comes up =iferror(A/((B/5)*C),"") The error comes up as C is someti

  • Pivot Table Calculated Field: % of Subtotal 2014-05-04

    I've created the above pivot table and no matter what I do I'm unable to create a calculated field in column D of the pivot table. In cell D5 I am trying to get the percentage: C5/C8, In cell D6 I'm trying to get the percentage: C6/C8, etc. I can cal

  • Fixing Pivot Table Select Field Value Formats 2014-11-04

    I've got an issue with Pivot Tables in a large file. At some point, I had a crash. The file was autosaved. Sometimes following autosave, my cell formats shift from General to Date. I've read about this shift elsewhere and found that this can be corre

  • Pivot table - Calculated field 2012-01-23

    Is it possible to create a pivot table that sums all fees and then create a calculated field that sums the fees based on a another fields requirements? For example: =IF('Type'="Associations",Sum(Revenue)) - I can't seem to get the 2 to work in the sa

  • Pivot table caculated field 2012-02-17

    I have a pivot table where I'm grouping by a column that would give either a yes/no response. For the yes group, i need to use the Max function to give a result; for the no, I need a sum. Is that something I can do with a calculated field? If so, how

  • Excel 2010 - Retain Pivot Table Value Field Conditional Formatting 2012-07-07

    I have an Excel 2010 pivot table with several value fields to conditional format. I want to the conditional format to persist after the user drags the field out of the values section and then back into the values section. When I do this, the confidit

  • Pivot Table no Field List 2012-09-24

    Yesterday I managed to make a pivot table. Today, when I do it, I select the area with everything, but no field values show up on the next page. Yesterday I dragged the fields to the pivot table and it all looked good. I don't really know how to set

  • Excel 2010 pivot table row field returns blank when source field larger than 255 characters 2013-02-24

    In the past, when you included a text field larger than 255 characters in the row labels section of a pivot table, Excel would truncate the field to the first 255 characters. Now, in 2010, I find it won't display the text at all. Is there some settin

  • Pivot Table Calculated Field results in error when data updates 2013-03-17

    Has anyone any suggestion as to why a pivot table returns the Name error in calculated fields after additional columns are added to a database. To recreate the problem: a) Create a simple database - Field names: Product, Quantity and Price; b) Insert

Copyright (C), All Rights Reserved.

processed in 0.089 (s). 11 q(s)