Closed Thread
Results 1 to 32 of 32

Populate a Dynamic 9 BOX CHART with Employee Names

  1. #1
    Registered User
    Join Date
    05-31-2017
    Location
    Denver
    MS-Off Ver
    10
    Posts
    7

    Red face Populate a Dynamic 9 BOX CHART with Employee Names

    Hi,

    I'd like to Automatically Populate a 9 BOX PERFORMANCE GRID using data from Excel.

    I'd like to use slicers if possible, to change who appears in the boxes based on some criteria (i.e. lets look at all Managers, now all Sr Professionals, etc).

    I can't seem to figure out how to get the data (Employee Names) to sit in the Boxes without cutting and pasting. I have over 900 employees.


    Thanks,
    Claudia

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

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Attach a sample workbook. 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.
    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
    05-31-2017
    Location
    Denver
    MS-Off Ver
    10
    Posts
    7

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Adding an attachment of what I'm looking for.
    Attached Files Attached Files

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

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    I'm working on this and it looks like I can come up with a solution, but I have a question. Is this 3 x 3 grid something they teach in HR school? This is the third time I've seen something like this in this forum . I'm more of an engineering type, I know little or nothing about HR.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    I had to make some changes to make the program work.

    First of all, I converted the data range to an Excel Table (Table_Data). Some of the advantages of tables is that they know how big they are so formulas and pivot tables that reference them always reference the exact amount of data that they need.

    Another advantage is that tables automatically copy down formulas, formats, validations, etc.

    I added some data validation to the table. Column B validates against the item column in the Table in columns A:C on the Lookups sheet. I also use this table to determine the columns and rows for the placements.

    I changed the data in Table_Data to reflect these values. The headers had spaces, whereas the data did not.

    When you add a new person to Table_Data, the validations copy automatically. You will get a drop-down list of placements in column B, you will get a drop-down list of Functions in column C (looked up from the table in column F on the Lookup sheet) and Column D is limited to a positive whole number.

    Once you get things set up on the Lookups Sheet, you don’t have to go back there.

    I also have a pivot table on the Lookups sheet that I use to manage the names and to trigger when the chart has to be redrawn. You can hide the columns containing this information if you want. In fact, when you are “done” with the Lookups sheet, you can hide the entire sheet.

    On the chart sheet, when you select data in the slicers, the pivot table on the lookup sheet changes and this launches the program to redraw the chart.

    I left the old chart sheet in the workbook. You can delete it if you wish.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-31-2017
    Location
    Denver
    MS-Off Ver
    10
    Posts
    7

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Oh my goodness!! I will dig into this and try to learn what you did, but want to give you a quick message and tell you that I LOVE YOU!

    It's basically a performance factor grid and we do cross-calibrations across the organization. So we'd say, let's look at all our Associate Engineers, Staff Engineers, Sr Engineers, etc, then let's calibrate all our Mechanical Engineers, Electrical Engineers, Quality Engineers. And if I can move these people dynamically (thanks to you) it can be a rich and meaningful leadership tool. To not only re-calibrate if someone is "off" next to their peers, but also as a developmental tool.

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

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Good luck. If you have any questions on how it works "under the hood" just ask. I promise I won't get your hands too dirty.

  8. #8
    Registered User
    Join Date
    05-31-2017
    Location
    Denver
    MS-Off Ver
    10
    Posts
    7

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    It's working great so far.

    I am looking to add a few more slicers, maybe 3-4. I think I've got that.

    But, where do I change the height of each box on the grid?
    Last edited by CompensationClaudia; 06-01-2017 at 06:08 PM.

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    The height of the box is automatically calculated by the contents. I could add a check to see what that winds up being, and assign a minimum height. Or if you wish, I can make it a fixed height (I'd have to comment out a line of code).

  10. #10
    Registered User
    Join Date
    09-05-2018
    Location
    Tennessee
    MS-Off Ver
    Office 16
    Posts
    3

    Wink Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Dear DFLAK, You're my hero. Love this spreadsheet. It is exactly what I was looking for. You could charge people for this. Thank you so much. Rachel

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

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Just paying back. A lot of people have helped me get to where I am now.

  12. #12
    Registered User
    Join Date
    09-05-2018
    Location
    Tennessee
    MS-Off Ver
    Office 16
    Posts
    3

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Hi, I've broken your spreadsheet twice, and I wanted to see if you could help me. Now all the names from every group show up in every box.

  13. #13
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Attach the broken copy and I'll see where it is broken. Normally when things like this happen (works sometimes doesn't work others) it has something to do with the data being fed to it. I'll have to look to see if there is a "gotcha" in the data and teach the code how to handle it.

    It would work best if we had the data as close to actual as possible. One thing you can do is use CTRL-H and Replace John Smith with Employee 01, Jane Doe with Employee 02, etc. Eliminate employee names you do not want to convert. We'll probably need about a dozen names to test it and hopefully one of the dozen is the culprit that causes the problem. Do try to pick records that look "funny" when compared with other records.

  14. #14
    Registered User
    Join Date
    09-05-2018
    Location
    Tennessee
    MS-Off Ver
    Office 16
    Posts
    3

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    I fixed it. Something to do with the columns in the lookup tab.

  15. #15
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Good for you. You are on your way to becoming an Excel Expert.

  16. #16
    Registered User
    Join Date
    11-01-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    2016
    Posts
    3

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Hi, this is exactly what I am looking to do but with less ratings. I would want to create a template that we can populate with different group names, and different ratings since we only use 5. is there an easy way to modify this? I tried but it ended up breaking some of the code. thanks!

  17. #17
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    I'd be glad to help you out. However, it is confusing to "hitchhike" on other posts. Please post a new thread but copy / paste this URL into it as a reference. Also post a sample workbook with it,

  18. #18
    Registered User
    Join Date
    11-08-2018
    Location
    IL
    MS-Off Ver
    2016
    Posts
    1

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    This is great. I have a potentially dumb question.....how to you build the chart on the Chart tab? This all makes sense to me except for the display.

  19. #19
    Registered User
    Join Date
    11-21-2018
    Location
    NY, USA
    MS-Off Ver
    windows 10
    Posts
    1

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    How do you change the column width and row height to a fixed value? I tried to change the code but the program froze

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    junze welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  21. #21
    Registered User
    Join Date
    08-13-2009
    Location
    Glasgow
    MS-Off Ver
    Excel 2016,2016,365
    Posts
    92

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Hello,


    I am trying to replicate what you have done in another workbook.

    The bit that that I'm struggling with is when you select a name on the Lookup Sheet, how does that name appear in the correct cell on the Chart sheet? Where is the connection between the Lookups sheet and Chart Sheet?

    Thanks


    Ross

  22. #22
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Hi,

    Is it possible for you to share the excel attachment with the workings(formulas) please.

  23. #23
    Registered User
    Join Date
    03-29-2019
    Location
    USA
    MS-Off Ver
    13
    Posts
    2

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Hey everyone,

    just trying to get access to some of the formulas so I can start deciphering the advanced uses of these formulas

    Thanks

  24. #24
    Registered User
    Join Date
    08-19-2019
    Location
    Alabama
    MS-Off Ver
    Excel 10
    Posts
    1

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    You are all my hero! Just googled, registered and now I am off and running.

  25. #25
    Registered User
    Join Date
    10-22-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    1

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Hi DFLAK,

    thanks for this macro, it just amazing and it works perfectly and its just exactly what i was looking.

    i have one issue, i could not see all of the employee names in the cell, since cell height restricted to 409 (i.e i can see approx 25 employees in the cell)

    Please could you how i will be able to see all the employees who sits in the cell. (in some of the category i have 150 employees). i wanted to show all the 150 employees in the grid.

    i do not want to merge the cells (rows), since it does not fit in one page, is hat any way i can add the names into the next column if the row height exceeds?

    or please guide me is any other way we can do it. i have just learning VBA now.

    Thank you so much for your help.

    Thanks

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    No help to be offered here - please wait until a new thread has been started. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  27. #27
    Registered User
    Join Date
    12-12-2019
    Location
    saudi arabia
    MS-Off Ver
    win 10
    Posts
    1

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    very good, i have been looking for this long time. Thank you all.

    One additional bit, how can i add a percentage of the total performance in the box

  28. #28
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Hello aboalbara and Welcome to Excel Forum.
    Please read AliGW's post (Just above your post)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  29. #29
    Registered User
    Join Date
    01-13-2020
    Location
    usa
    MS-Off Ver
    360
    Posts
    1

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    hi dflak,
    this really is a very efficient excel sheet to autopopulate 9 box grid with team members. I am interested in finding out how this is working under the hood. I understand the slicers part but what I cant figure out is how the slicer filter data is autopopulating into the grid.
    Thanks in advance

  30. #30
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Administrative Note:

    Hello ssingh91 and Welcome to Excel Forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    Let us know if you have any questions.

  31. #31
    Registered User
    Join Date
    04-24-2020
    Location
    Delhi
    MS-Off Ver
    2017
    Posts
    1

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Its a great thread. I am looking for a similar kind of solution, where based on the data, it reflects in the 9-Box table.

  32. #32
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Populate a Dynamic 9 BOX CHART with Employee Names

    Administrative Note:

    Hello karthik93 and Welcome to Excel Forum.

    As has been mentioned a few times in this thread:
    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    Let us know if you have any questions.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to group and display employee names - dynamic list
    By nitin.asalkar in forum Excel General
    Replies: 1
    Last Post: 01-20-2017, 05:26 AM
  2. Replies: 0
    Last Post: 06-25-2015, 06:24 AM
  3. Populate sheet names in RibbonX dynamic dropdown
    By 2Excel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2011, 04:51 PM
  4. Populate a chart with various names on a worksheet
    By shuttletech in forum Excel General
    Replies: 2
    Last Post: 01-13-2011, 11:54 AM
  5. Solving wrong displayed value in chart using dynamic names
    By psylion in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-10-2010, 07:06 AM
  6. [SOLVED] Dynamic Step Chart using range names
    By Michel Gerday in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 02-28-2006, 09:10 PM
  7. Chart with dynamic x-axis names
    By Jon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2005, 10:06 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