Using Excel Vlookup With Shapes and Data Validation: Building a Dynamic Salesman Dashboard (Part 2)

For the purpose of this post, here is a quick how-to on how to use table fields as your validation list in Excel.

• Build a table out of your data analysis range.  To do this, you can select any cell within the range of data and type Ctrl + T (windows OS).  You’ll be prompted with a text box, make sure “My table has headers” is checked.  This will use your headers as table names.

• Quickly identify how you’ll need to refer to the table field you’d like to validate against using the = symbol within a cell and then selecting the entire column/field within your table.  This is a great way to determine the syntax required when creating our name range – as you can see in our case our range will be =Table1[[#Data],[#Totals],[SALESMAN]].

• Now that we’ve copied our table name reference range syntax, copy this information and create a name range with the range syntax from the formula tab within the ribbon (name range).

• The next step is fairly simple.  As per my last post on setting up a data validation list once you are located on the cell where you’d like to place the data validation list, go to the data tab in the ribbon, then click the data validation.  Enter your validation criteria as shown below, then use the name of the newly created name range for your table field/column (SALESMAN).  Click OK.

That’s it!  You may find this method a little less cumbersome than the offset method.

The first thing we’re going to do is make our display dynamic.

We are going to add a couple of boxes.  One box will be a static text and the other is going to read and change dynamically based on the user’s selection from our validation list.  The first box will be built by simply creating the box, selecting the box and then in the formula bar typing the = symbol and then selecting the cell where our validation list is located.  This will automatically allow our information in that text box to update automatically.

We will then create a copy of that exact same box, remove the formula and then just simply add our text.  Once we’ve blended these two boxes together, you can then group them by selecting both boxes (shift + select both boxes) and then right clicking and grouping them.

Our next step will be building a reference data sheet that is going to show the total sales based on the value selected on the dashboard spreadsheet/tab.  The data selection field will be a linked cell to our data validation list cell.  Our second display value column will use a Vlookup formula to lookup the data selection value (cell to the left), it will then find that salesman/data selection within our sales data tab and we will then display the total cell value for that salesman.

FOR MORE ON HOW TO USE VLOOKUP CLICK HERE!  This is a great video post on Vlookup by Danny Rocks!

Finally the last step of the process is to build the dynamic yearly sales value shape (based on the user selection) display based on the data provided on our reference numbers spreadsheet.  Create the shape where the dynamic sales value will be displayed on the dashboard spreadsheet and link the shape to our reference numbers display value cell.