Forum Discussion

allodiba's avatar
allodiba
Copper Contributor
Feb 02, 2024

Referencing a range within a pivot table field(s)

Hi all. I am working on a spreadsheet of survey data. The survey asks respondents a number of questions to gauge their retirement readiness and confidence in health and finance. The end goal is to...
Formulas and Functions
  • djclements's avatar
    djclements
    Feb 03, 2024

    allodiba Looking at your screenshots, it seems like you could just move the Answers field into the Columns Area of the pivot table report.

     

    Having said that, the entire report could simply be built with the COUNTIFS function, rather than having to mess around with Power Query, Power Pivot and/or pivot tables.

     

    Please see the attached file, which is setup as an "all-in-one" report, where you can generate results by Marital Status, Retirement Timeline or Age, or any combination of the three. You can also use Filters on the results range to sort by % Yes or % No/Sort of, as well as to filter by Number Filters > Top 10...

     

    COUNTIFS Report Sample

     

    EDIT: in the attached file I've also included a second worksheet called "Report with Blanks", which has been modified to accommodate any records in the data table containing blank values (ie: if the Age field is left blank, or if an answer to any question is left blank). If this is not a concern, the "Report" worksheet will be sufficient. Cheers!

Lorenzo's avatar
Lorenzo
Silver Contributor
Feb 03, 2024

Hi allodiba 

 

Before everything else: please always provide the version of Excel you run + OS (Windows / MacOS)

 

#1. Is it possible to reference an array under a specific pivot field? Example: in the screenshot, I'm looking to pull the % for those who answered 'no' and 'yes' with =VLOOKUP("No",A3:B5,2,FALSE))

Basically the answer is no. Depending on your version of Excel there's probably a +/- complex solution though

 

#1.1 Is there a way to reference the pivot table fields Questions and Responses with =GETPIVOTDATA?

No problem. However as you will notice with the below example things are very hard-coded between double-quotes:

=GETPIVOTDATA("[Measures].[Answer%]",$B$2,"[Table1].[Question]",
	"[Table1].[Question].&[Q1. I feel I am in good health currently.]",
	"[Table1].[Answer]","[Table1].[Answer].&[No]"
)

So, if on a next Refresh of your PivotTable a Question has changed the above formula will need to be adjusted

 

#2 My team has decided that a non confident answer (for now) will be the responses containing "no" and "sort of"

 IMHO your team already has the required "tool": build a new PivotTable > Put the Answer field in the Filters area. Then filter on no and sort of only:

If later your team change their mind they will only have to play with the filter. Makes sense? 

Resources