Results 1 to 3 of 3

pivot formula: Reference to cell outside of pivottable?

This is a discussion on pivot formula: Reference to cell outside of pivottable? within the Excel Questions forums, part of the Question Forums category; Hi all, I have searched and searched. Unfortunately i haven't found an answer to the question; how (IF) can i ...

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Question pivot formula: Reference to cell outside of pivottable?

    Hi all,

    I have searched and searched. Unfortunately i haven't found an answer to the question; how (IF) can i reference a field outside of the pivot table in a pivot table field formula?

    What do i want:
    To compare a calculated field in the pivottable [that contains percentage of row] to 'Activesheet!$A$1'
    If the calculated field is higher than cell value i want to show that field.
    I want to hide all the fields where it is less or equal to that value


    Problem1:
    When i try to reference the 'Activesheet!$A$1' field inside of a pivot table formula it states no reference can be made ('references to matrix, named...' are not supported...')

    Problem2:
    I can't select a calculated field as a filter criteria?
    (i can work around this by using the calc field in table and then setting filter on that column, so this is less of a problem than problem 1)


    if Problem1 is impossible to solve:
    Is there a way to get the total number of columns and use that in the pivot table formula?

    If an example is needed let me know and i will provide one (the workbook this is all in is quite heavy with vba code; so will create one without, so it is more clear than).

    Thank you kindly!,
    deduijk

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,918

    Default Re: pivot formula: Reference to cell outside of pivottable?

    hi, deduijk

    Say the current PT source data is named (non-dynamic) MyData

    Put a header above the single cell. Give it a non-dynamic name also. Say, MyCell

    There will now be two named ranges. MyData with headers & data and the small table MyCell that has a header and one data item.

    Manual approach. Create a pivot table from a new Excel file. At the first step of the wizard (ALT-D-P) choose the external data option. Follow the wizard to the end and choose the option to edit in MS Query. Edit the SQL to suit. Obviously the SQL will be specific to your set up. Its form will be something like,
    Code:
    SELECT M.your fields
    FROM MyData M, MyCell C
    WHERE [your calculated field] > C.[field name]
    (Though a little more complicated as the calculated field is a percentage of a subtotal.)

    Complete the pivot table and move the resultant worksheet into the source data file if you want.

    HTH

  3. #3
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Default Re: pivot formula: Reference to cell outside of pivottable?

    Thanks! i'll give it a try.

    I assume it works with a dynamic (named range) as well?
    Last edited by deduijk; Mar 13th, 2012 at 08:40 PM.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com