+ Reply to Thread
Results 1 to 5 of 5

Dynamic Pivot Table - 9 Box Grid

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    Bath, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Dynamic Pivot Table - 9 Box Grid

    Hi,

    I am trying to create a dynamic 9 box grid table that will re-size depending upon the population selected via a pivot table. I attach the example table in Sheet 1 and some example data in sheet 2.

    All employees who are marked as a '1' in Sheet 2 will appear in the top left corner of the spreadsheet and all '9's will be in the bottom right etc. Ideally I would like someone to be able to use the PIVOT table on the PIVOT tab to select the population, e.g. all London Accountants and these names would then populate the relevant portion on the grid. The max total population will be close to 2,000 names (spread across all 9 boxes in the grid), but some groups may only be 5 or 6 people.

    Is this possible?!

    Thanks

    cd254
    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,108

    Re: Dynamic Pivot Table - 9 Box Grid

    I notice that on your grid, you have 9 slots for each category, Are these fixed or would you like them to be dynamic?

    Also the layout of the grid is a bit odd. It doesn't go in order. In other words, I was expecting to see 1-3 going across then 4-6 under it, then 7-9 under that. Or 1-3 going down column A, 4-6 going down column B, etc. However, I suppose you have a reason for organizing it this way.
    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
    Registered User
    Join Date
    04-11-2013
    Location
    Bath, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Dynamic Pivot Table - 9 Box Grid

    Thanks for responding.

    I'd like the slots to be dynamic (that was just an example sized grid). When the total population is included there will be 2,000 names spread throughout the whole thing, so there could be 200 names in box 1 for example. I'm looking for something that will map the names into each box and then resize the table to fit - if this is possible!

    With regard to the numbering order, this is intentional although its a long story to explain why...

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

    Re: Dynamic Pivot Table - 9 Box Grid

    I went with the assumption that you wanted it to be dynamic.

    I converted the data into an Excel table mainly because they are dynamic - when you copy in your real data, all the formulas and the pivot tables will adjust to the new "size" of the data. Also, it's easier to write formulas using table references.

    So the pivot table's new source is Table_Data.

    Since you have Excel 2010, you also have slicers. Slicers not only look cool, they can also be used to synchronize multiple pivot tables at once. Otherwise, this would have to be coded. I have some pivot tables on the parameter sheet that get set to the same filters as the slicers. They also do a further filter for the score. The code runs these in sets of three. I also have a lookup "table" that tells me how many of each score I have in the data. If it's zero, I can't set a filter for it and I don't want to process it in the code. The Parameters sheet is used for "scratch" calculations like getting the title for each section and can be hidden if desired.

    The report is fired off when any of the filters is changed on the pivot sheet. It doesn't matter if you use the slicers or the actual pivot table filters.

    The final piece of code provides the titles.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-11-2013
    Location
    Bath, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Dynamic Pivot Table - 9 Box Grid

    Perfect! Thanks so much

    cd254

+ 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. [SOLVED] Dynamic Pivot table
    By chandu356 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-19-2014, 04:57 AM
  2. [SOLVED] updating dynamic table from pivot table
    By Bax in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-20-2013, 06:31 AM
  3. Dynamic Pivot Table
    By Alma in forum Excel General
    Replies: 0
    Last Post: 07-27-2011, 03:40 PM
  4. Excel 2007 : Pivot table grid lines
    By rraze in forum Excel General
    Replies: 1
    Last Post: 07-06-2011, 05:27 AM
  5. Pivot Table based on dynamic table
    By CydMM in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-28-2010, 06:59 AM
  6. Dynamic Pivot Chart based on Pivot Table.
    By excelkeechak in forum Excel General
    Replies: 3
    Last Post: 12-01-2009, 09:23 PM
  7. Dynamic pivot table
    By Jon Haakon Ariansen in forum Excel General
    Replies: 3
    Last Post: 03-17-2006, 10:10 AM
  8. Pivot Table Lookup Grid
    By MIVELD in forum Excel General
    Replies: 1
    Last Post: 09-13-2005, 01:05 PM

Tags for this Thread

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