Display a PivotTable slicer based on cell value

[geester]

Hi
This is the 1st question i have posted so i apologise in advance if more info is required

Basically I'm trying to add a Slicer to a PivotTable chart based on a cell value
so for example if someone types North in cell A1 then the slicer named North will be displayed

[southward]

Hi,

My macro skills are limited to using macros that are created with "Record Macro" via the Developer menu.
After you have your PIVOT Table in place, start the recorder before setting up a slicer the way you want it.
The recorder can then be activated via a Worksheet_Change macro.

I realized this is a very simplified answer to you question.
Yes, it is possible to do what you are asking.

For a more detailed solution you will need to provide a (sanitized) working copy of you spreadsheet.
With more information I am sure one of the Macro Guru's around here will jump at the chance to help you out.

Cheers

[geester]

Thanks for the info i recorded a macro this is the code it generated so if someone types South in cell A1 then the slicer named South will be displayed if they type East in cell A1 then the slicer named East will be displayed

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Untitled.jpg

[geester]

I would upload an example if i could when i click on the attachments icon for uploading a file it just looks like a very thin text-box with nothing in it

[Richard Buttrey]

Hi,

The paperclip icon doesn't work - not sure why. However go to the area below the post and choose the Manage Attachments option and take it from there.

[Richard Buttrey]

Welcome to the forum

You've complicated this unnecessarily with the data layout you've chosen.

The data for Pivot Tables should have columns for each TYPE of thing, NOT for each unique value within a TYPE.

So your Data columns should be

Name
Days (or for future proofing I'd be inclined to put a date in here)
Compass Point - i.e. values in this column would be North/South/East/West
Value

This table will contain all the same information but you'll save all the white space.

When you create a PIVOT Table you'll then use the Compass Point as the Slicer - which is the correct way to make use of it.

[geester]

Hi Richard
Thanks for the info
i understand what you are saying in the actual workbook i'm working on the pivot table is well away from the chart and the data is in another sheet
The real data has nothing to do with North south east or west
i have just tried to create a very simple example that reflects what im trying to do

I want to type the name of a column from the data into A1 and the correct slicer will be displayed

[Richard Buttrey]

Hi,

I think you may have missed the point.

You don't have four slicers, you have one which takes one or all of four values. It doesn't matter where the data, PT or charts are, they can be anywhere. What's important is that you capture your data in the correct format so that you can analyse/summarise it.

See attached where I've re-organised your data on a sheet called Data and added a PT, chart and a slicer that allows you to pick one or all of your four 'types'.

[geester]

Thank you very much for the help but my problem is not about how the data is organised
what i want to do using the example you have reorganised is type "Names" in A1 and the slicer "Names" appears.
In the workbook that has real data there are over 60 named columns
I do apologise if i'm not clear as to what i.m trying to do

[Richard Buttrey]

Yes, but what I'm saying is that the Slicer REPLACES the drop down cell.

Remember fields in the PT can be dragged and dropped around to show whatever relationship you want.
In the attached I've merely dragged the Days column into the Column Labels area of the PT so that it looks like your original. However unlike your original it's far more flexible. It's just one view and you can move fields around the Field List window as you want.

For your real data the Slicer will contain all 60 'columns' except of course that they are not Columns in the data, but a single column in the data where each row in the table identifies one of the 60 Types and the Value column holds the 'X' or whatever it is in your real data that's the equivalent of the Xs

I know PTs are sometimes a difficult concept to get ones head around, but once you have set your data up in a normalised 2 dimensional table they take mere seconds to create and avoid functions and VBA altogether.

[geester]

Richard Thank you for all your help
i'm trying to avoid the end user selecting a slicer by clicking on the pivot table or chart and going to the ribbon selecting analyse then selecting the slicer
if i can call a slicer by a cell value i have code that will put the column name in a cell
i hope it makes sense as to what i'm trying to do

[geester]

Found this thread it looks he was trying to do something similar
http://www.excelforum.com/excel-prog...-assigned.html

[Richard Buttrey]

Hi,

If you don't want the user to pick from a slicer then adding this macro will allow you to pick from a cell drop down. See attached

Please Login or Register  to view this content.

[geester]

Hi thank you very much that could be very useful but instead of selecting a filter from a slicer i want to be able to select a slicer so if i put Names in (Target, Range("selected")) then the slicer titled names would appear the user could then filter from the slicer

I do appreciate all your efforts and help

[Richard Buttrey]

Clearly we don't have the same concept of what a Slicer means in the context of a Pivot Table and I'm struggling to understand what you would expect to see if you were to have say a Slicer called 'South'
What values would you expect to see within that Slicer? All the data in the column called South are values "X".

The A1 cell on the Data sheet is a drop down cell in much the same way as your A1 entry cell on sheet1 and which you used to create your 'Pivot Table' - although I don't understand how in your example for SOUTH that you get 2 Mondays for Alf when there's only one instance of South/Alf/Monday - as identified by the new PT I showed on the Data sheet.

[Richard Buttrey]

Hi,

I've seen your PM where you say that the example you posted is a much simplified one. That's why we always like to see examples of the real data and not a trivialised example that doesn't truly reflect your production data.

All that said, this doesn't alter my belief that the data table should still be organised in such a way that a real Pivot Table, not some pseudo PT that you currently have, could be used. As I said earlier in a database for a PT it's important to distinguish between fields of information and the values that those fields may take.

Now that you've said that what were originally four directions (N/S/E/W) in your trivial example are in fact 52 weeks of the year this makes it even more important, if you want to do any serious analysis and not complicate matters in the future, that you normalise your data into a standardised two dimensional table of Fields and Rows.

You also appear to be over complicating your data by having a column for Days (Mon/Tues/Wed..etc) as well as data to represent weeks 1-52. Since a Date can be formatted to show as a Day as well as a Date and only needs to be recorded once

Your data should consist of columns for
Date
Name
Value (H1, H1, H2...etc)

You would then probably have two slicers, one for Date and one for Value, and for complete flexibility you may as well have the Name as a Slicer.
Even if you don't want the user to use the Slicers and only pick from cell drop downs, then you would simply use the change event of the cell drop down to run a macro which changed the Slicer as if it had been changed manually.

[geester]

Hi Richard I'm very sorry for the poor example i was focusing on how to display a PivotTable slicer based on cell value regardless of data layout and content

The data is extracted via macros from another workbook .The data in the source workbook is a mishmash of merged and non merged cells spread over many worksheets to be honest its a nightmare i'm 99% done
i just need to work out how to display a PivotTable slicer based on cell value
If a user could put a week number in a cell on the pivot chart sheet hit a button and the slicer that equals the cell value is displayed the user could then select the items they want to filter


its took years for me to post something think i will stick to reading the posts in future

[Richard Buttrey]

Hi,

I don't envy you having to deal with merged cells across a multi sheet workbook. We've all been there at some stage in the past. Most of us avoid merged cells like the plague. They really do cause too much trouble.

Why don't you upload an example of your actual workbook. It doesn't need to be the whole workbook as long as it is a representative subset of your data.

In a note explain where and what you expect the user to enter and mock up an example of how you would like the data to appear as a result of the users selections. Don't try and get a Pivot Table to produce the result. Let's worry about that when we can see what you expect to see in the layout and format you want.

[geester]

Hi Richard


My idea is the user will put the week number they want to filter into a cell say K2 for example
The user will press a button which will show the slicer that is named the same as the week No in cell K2
the user can then manually select the items in the slicer
if they put a different week number in and press the button the slicer filters will clear and the slicer disappear
a new slicer will appear that is the same name as the new week number

tried to add an attachment not sure what i've done wrong

here it is

[Richard Buttrey]

Hi,

When replying to a post you need to select the Go Advanced option, then look below the post for the 'Manage Attachments' option and take it from there.

[geester]

I have been playing around trying to solve how to display a slicer that matches a cell value
the only way i could figure out how to do it is to put all the slicers on top off each other
then whichever slicer shape name matches the cell value is brought to the front

[Richard Buttrey]

At the moment you don;t have your data in the sort of layout that a PT can use.
The table should be a normalised two dimensional table of unique field column labels and row records. The column labels should be UNIQUE TYPES of data not unique values for the SAME type of data. For instance currently you appear to have what appear to be week numbers across row 6 of your data. You shoudl have a SINGLE column for Date and within that column you would hold the Week number, (or preferably a date since this will be far more useful when it comes to analysing/summarising)

So create a table which consists of columns for
Date
Area
Name
Value ' i.e. this column will contain your H, FD, RI etc.. references.

When you have that then post back

I'm slightly puzzled though since the latest file you've uploaded seems to be completely different and I'm guessing this is a new request entirely. We have a rule of one subject, one thread. If it is indeed new then let me know and I'll move this latest request into a thread of its own.

[geester]

Hi Richard
it is the same issue i know the table is not normalised the real data is imported from a workbook that i can only describe as a car crash in terms of data structure it has merged cells they use numbers 1- 53 for weeks no dates the first letter of each day represents Monday to Friday
I get told it cannot be changed because it has always done that way and the person that inputs the data likes it the way it is crazy i know
I have uploaded the example to show how i got around my particular problem its only used as a quick visual to show how many people are off in each area on any particular week