+ Reply to Thread
Results 1 to 6 of 6

Slicer that will select a column

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    England
    MS-Off Ver
    Excel 2011 Mac
    Posts
    5

    Slicer that will select a column

    I am pretty new to pivot tables and am struggling. I have a massive spreadsheet with assessment percentages in for 29 different subjects going across the columns. What can do is slice, by say gender, but i also want to slice by subject as well so that my tables will pre-populate

    I don't want to have to create multiple copies of the "Art" sheet and then keep changing the subject

    What am i missing?
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,081

    Re: Slicer that will select a column

    By "subject" you mean you want to be able to select Art, Business Studies, Citizenship, etc. I presume. Your data is not set up to support this. The data needs to be "normalized" into a structure that looks like:

    School | Gender | PP | Subject | Number

    You'll need a program to translate the Master Sheet into this format. I've done this before, but each case is slightly different (depending on how many "fixed" things like School, Gender, PP there are. I can find one of these and adapt the code perhaps.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,081

    Re: Slicer that will select a column

    Since you don't have a quantity I will assume that the information in columns A:H are "fixed" - you don't need to split them out like subjects (Columns H:AJ).

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,081

    Re: Slicer that will select a column

    Here is as far as I can take it at the moment. There is a macro called "Normalize Data." It probably takes 5-10 min to run. I did not run it all the way, I only ran about 1/4 of the data and then had to tweak the schools to get a distribution.


    I've built pivot tables off this table. Since I have the data organized differently, I can't get the last two pivot tables (% @ each stanine
    and Cumulative %) to work. If you can give me a definition of what these figures are supposed to represent, I can either add helper columns to the source data or revise the pivot table to provide the figures.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-16-2014
    Location
    England
    MS-Off Ver
    Excel 2011 Mac
    Posts
    5

    Re: Slicer that will select a column

    the pivot tables were built from the stanines sheet. The % of each stanines are for example how many art students are in stanine 1, stanine 2, up to stanine 9.

    Cumulative is how many at 1 and above 2 and above and so on

    thanks for your help so far

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,081

    Re: Slicer that will select a column

    I thought differently on this on the drive home.

    I added a helper sheet called Parameters. It has a list of all the subjects in Column A. I overlaid this with a named dynamic range that is used for data validation in Cell L2 on the Subject worksheet.

    When you make a selection in this field, it computes the offset in cell D2 on the parameters sheet.

    This offset value is used in a helper column (the very last column on the Master sheet – currently AK) to get the value associated with the selected subject. The column header name for this is “Selected.” I use it in place of “Art” in the pivot table.

    When you select a new subject, the offset changes and the values in the Selected column changes.

    I had to add a change event on Cell L2 on the parameters page to “force” a pivot table refresh.

    The data on the Master sheet is now an Excel table and the pivot tables use it as a data source. If you insert a new subject, the pivot table will adjust to the new range. Make sure you also add the subject to the list of subjects on the Parameters sheet.

    There are some minor issues with the pivot tables. I seem to be picking up blanks. Perhaps these can be filtered out. Otherwise the data seem to match perfectly.

    Also the HML has blanks instead of “O.”

    I’d be interested in your explanation of how you cleaned up these two items.

    Let me know if this meets your needs. Unlike a true slicer, you can only select one subject at a time, but I think that’s what you want. It certainly sticks closer to the original functionality and eliminates the need to renormalize the data whenever anything changes.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Catastrophic Error with Slicer To Select x Days
    By Tellm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2015, 05:56 AM
  2. Have a cell equal to the value I select in a slicer?
    By qlikview in forum Excel General
    Replies: 1
    Last Post: 07-07-2015, 02:56 AM
  3. VBA code to create input box that will select one value in a slicer
    By Ind711 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2015, 04:19 PM
  4. Newbie: Auto Select/un-Select an Range of Slicer Items
    By luckyali in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2014, 09:52 AM
  5. Select fields in slicer with macro
    By joelvankesteren in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-13-2014, 02:21 AM
  6. How to auto select from a Slicer last or top item
    By xtrenge in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-07-2014, 06:22 PM
  7. 'select all' option in slicer
    By Paul-NYS in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-24-2012, 09:23 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1