Populate a Dynamic 9 Box Chart with Employee Names over 409 row height limit

[Lois555]

Good morning - never posted before, so hope I am doing this right.
I found most of my answer on the thread of
"Microsoft Office Application Help - Excel Help forumExcel Charting & Pivots Populate a Dynamic 9 BOX CHART with Employee Names", posted by CompensationClaudia and answered by dflak in June of 2017.
Since I am new, I am not able to post links, but the answer I found earlier told me to put the link of the thread I found most of my answer to in this post. so.........

My 9 box works except for row height & merging cells. I have almost 60 employees in one box - the Excel limit of 409 does not let me see them all.
Is there anywhere I may put in the code to either merge cells without losing the other boxes' data or create two columns to allow us to see all employees?
Thank you & again, hope I posted this in the correct spot....

[dflak]

I knew this title sounded familiar .

You should be able to copy / paste the link into the post. If not, try this trick. Copy and paste it to notepad and attach the text file to the post. We'll beat the system one way or the other. I don't know if I'll have an answer, but it will be fun trying.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Lois555]

Thank you for your expedient response!
I believe I attached them.
On the Chart tab, cell B4, we can't see all the 'Core Player's
We would like 2 columns or some way of merging the rows and then expanding the range as necessary without losing the employees in Risk, Avg Performer & Sold Performer.
Make any sense?
Thank you again and I hope I'm doing this right!
Lois

[dflak]

No attachment. There a a number of steps to attach the file, you have to go advance and then select it and then upload it and then clear the screen. It's easy to miss one.

[Lois555]

Better? (I hope!)

[Andy Pope]

This modification adds extra row and column to each square.

Names are outputted in 2 column list over 2 merged rows.

[dflak]

In case Andy's post doesn't work out (although I like what he has done) - I am almost there but I need to accommodate for slicer selections. I'm working with a different concept that does one person per ROW instead of collecting the names in the cells.

[Lois555]

Thank you both so much! I tried modifying the code I had with Andy's & I came close, but not quite - I am going to try again as soon as I finish some stuff for people here.
I really like the idea of one person per row - that way our managers could copy any box to a spreadsheet without all the names running together ( I have 11 of these 9 boxes for all our different locations. You all are amazing!
Thank you!

[dflak]

I think I finally got it. It is one convoluted mess on the Lookup sheets . I have a lot of "helper pivot tables" overlaid with named dynamic ranges. But it works.

[Lois555]

This is amazing - thank you dflak!
In the combined 9box that has all our locations, I have added a few columns to the table that I need to slice on also.
I added a column to this one & a slicer - not a good idea.
Is that an easy fix or something that should not be done?
Thank you so much for all your help!
lois

[dflak]

Attach your modified book. As long as the 3x3 matrix is intact, I should be able to accommodate the change - an extra column in the source data should not be an issue. It might be something as trivial as connecting the new slicer to all the pivot tables on the lookup sheet.

[Lois555]

Thank you!
I have added columns that I need on the total one to this one - I refreshed the sheet & the slicers don't seem to instigate the macro - could just be my machine....
Thank you again so much!!!

[dflak]

What is the difference between the file you sent me and the one I sent you? What "column" are you talking about? What slicers would you like to add?

[Lois555]

Thank you for asking-
I added columns M-P on the 'Atlanta' tab (trainee, hi, mgr & f) - I would like slicers on the 'Chart' tab for all of them.
Thank you!!!
lois

[dflak]

Ok got it. I worked on this assumption. I made slicers for them and then went to the slicer options and "connected" them to all the pivot tables. It seems to be working.

I think that you should be able to add any number of columns and slicers. When you add a new column and slicer, right click on the slicer, look on the ribbon for Slicer Tools, click on options. On the left of the ribbon, you will see report connections. Select all the pivot tables on the lookup sheet.

The Lookup page may be a mess, but by running the program off pivot tables, it turned out to be more flexible than I imagined.

[Lois555]

Wow - Thank you!!!! I have to go to a meeting but will look at this as soon as I can - I appreciate all your talent & work - Thank you!

[Lois555]

This is perfect!!! Thank you! I am applying it to all our locations and our HR is Very happy - Thank you!
What do I need to do to close the thread & review you as amazing?
Thank you!
lois

[dflak]

As for closing the thread, click on the thread tools link at the top of the sheet. If you wish to review click on the Add Reputation star at the lower left of the post.