Pivot Table help - Can I show certain data depending on value in another field

[Monty85]

I feel like I've seen this done before but I can't seem to work it out and am struggling in what terms to search for when looking for help.

What I want to do is have a dynamic Pivot Table displayed that will filter the data depending on a category that is selected from a separate field.

In the below example, if Cell B1="One" than the pivot table should only show the data for items that are in Category One (listed in Column E) - so only 3 rows would display.

Category Name:	One
ID	First Name	Last Name	DOB	Category
79​	R	Apple	1/04/1979​	One
30​	E	Banana	5/06/1980​	Two
36​	F	Pear	7/02/1998​	Three
96​	S	Orange	12/08/2001​	One
31​	G	Lettuce	15/10/1968​	Two
32​	Y	Carrot	20/03/1987​	Three
49​	D	Potato	4/07/1997​	One
12​	V	Onion	3/12/2002​	Two
51​	W	Tomato	25/06/1974​	Three

 

[RoryA]

If you put Category as a filter field, it will do that - e.g.

 

[Monty85]

If you put Category as a filter field, it will do that - e.g.

View attachment 106459

Ah thanks.

I still can't seem to get the Pivot Table to remain in the original format though. Guides i've found online just tell me to select the fields I want, but when i do that I end up with everything listed in one column or split incorrectly across rows.

Probably just need to play around with the options some more until I work it out.