You are here:

Excel/9-box grid / if/then


I am looking for someone who can write what I think is simple code for an Excel file.  

The purpose of the file is for a manager of a team to rate each member of his/her team based on his/her potential and performance to help identify talent development needs and for succession planning purposes.  

The manager receives a spreadsheet with a list of the names of his/her group (anywhere from 10-150 people). The manager then selects from a drop down box that has nine defined selections (peak performer, low performer, significant contributor, functional expert, etc.).  Right now, we're using data validation for the user drop down list and that works great.  

Once all the employees have been rated, we would like to auto-populate a 9-quadrant grid (3 rows of three) and return the names of each employee to the relevant box in the grid.  For example:  If in Column A the employee,  Mike Smith, was rated as a "peak performer", his name would show in the "peak performer" box in the upper right hand quadrant of the 9-box grid.  If the next employee, John Doe, was rated "Low performer", his name would show in the lower left-hand corner of the 9-box grid.  This would be true of all the staff on a person's team so that each person's name shows in the relevant box.  Each row of the grid would ideally grow to whatever length is required to return all the names that apply, but ensuring that the grid rows stay the same size so it looks like a tic tac toe grid.  Each box has a different color to visually display the results.  

I've attached an example so you can see what it looks like.  Would appreciate any advice you can offer.

Your picture was too big and when the forum squeezed the picture down in size it became unreadable.   You can use the link in my signature/profile to send me the actual file, I'd need to see it to make a good first-use suggestion.

I can fathom both a formula-based and a vba-based approach to both. Do you have a preference?

Formula-based will require adding some additiona helper columns to the raw data, columns that can be hidden if attractiveness is an issue.

VBA will look cleaner, but will require your workbook have macros-enabled.

Let me know.

This thread was solved with formulas and the 9x9.xls file is located in the FILES section of my site:
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques:


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: ===================== I have been offering free assistance as an Excel aid on many web sites for many years: ( - JBeaucaire) ======== ( - JBeaucaire) ======= ( - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 All rights reserved.

[an error occurred while processing this directive]