Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but have everything in the other field) and have the report populate.

Here is the an example of the raw data (the real data is thousands of rows).

Raw Data


Manager Product Period  Revenue

John    Rakes   Half 1  200

John    Shovels Half 1  100

Suzy    Rakes   Half 1  50

Suzy    Shovels Half 1  40

John    Rakes   Half 1  200

John    Shovels Half 1  100

Suzy    Rakes   Half 1  50

Suzy    Shovels Half 1  40

John    Rakes   Half 2  201

John    Shovels Half 2  101

Suzy    Rakes   Half 2  51

Suzy    Shovels Half 2  41

John    Rakes   Half 2  201

John    Shovels Half 2  101

Suzy    Rakes   Half 2  51

Suzy    Shovels Half 2  41

The pivot off of the data has manager then product for rows and Half 1 and Half 2 and total for columns. The rows have subtotals on manager and then a grand total.


                               Full Year

Rakes                        802              =SUM(GETPIVOTDATA($F$5,{"John Rakes H1","John Rakes H2"}))

Shovels                    402               =SUM(GETPIVOTDATA($F$5,{"John Shovels H1","John Shovels H2"}))

JohnTotal               1204              =SUM(GETPIVOTDATA($F$5,{"John H1","John H2"}))

I am looking to have the ability to have John Rakes H1 and John Rakes H2 in a cell where the user can just select the manager name (or all of the managers) and the product (or all of the products) and that simple report populates itself. Realize the real data is a ton of managers and products so manually creating the formulas doesn't work (nor does have many layers of nested if statements)

I thought I was really close but I can't seem to get anything within the { and } to be able to reference what is in a cell.

Help is GREATLY appreciated.

share|improve this question
    
Have you tried adding a Filter to the pivot table that allows the user to use the drop down to select the labels they want to see? –  Scheballs Jan 31 at 14:24
    
The end output is a report which has a ton of other information on it -- not the pivot table (but information from the pivot table). The pivot table is on another tab (hidden from the audience). Maybe you're hinting that there is a way? :) –  rich Jan 31 at 16:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.