Click OK.
If you play with the scrollbar, you’ll see that it keeps the indices within the correct bounds.
Step 6: Use VLookup to take largest values to its corresponding information.
In the first cell to the right of the Largest Values column (cell H32), we’ll use the Weight Value to the left as a lookup value. Now, do you see why I moved the final weighted scores all the way to the left on the ata tab? It’s a lookup column now! So, in Cell H32, you’ll have something like the formula shown below. But rather than typing what I have, try to recreate the formula yourself. Then double-check to ensure that our work agrees.
But wait, the VLookup Table on the example tab has a column for each metric, and we've only filled in info for one column. We’re not just interested in the second column from the table on the data tab. We’re actually interested in columns 2, 4, 6, 8, 10, and 12 as demarcated in red below.
We can actually pull all that info out with only one VLookup. On your data tab, select H32. Now, rewrite the VLookup formula as follows:
=VLOOKUP(G32,Data!$G$4:$R$53,{2,4,6,8,10,12},FALSE)
Note we have changed the formula from looking up only column 2 to looking up a set of columns. Ensure that you use the curly braces to surround your set of columns as shown above. Now drag the selected cell to the right until you reach column M, the end of the Vlookup table. With
H32:M32 still selected, click into the formula bar, then press Ctrl+Shift+Enter. Viola! We use the Ctrl+Shift+Enter here because the VLookup is returning an array of numbers and not just one number. Now drag the selected row all the way down to fill the table. If you’ve done everything correct so far, your table should look like mine.
Use the scrollbar to see the values change.
Step 7: Map the values from the intermediate table to the dashboard.
First, select the scrollbar to the left of your VLookup Table. Press Ctrl+C to copy. Scroll all the way up to the dashboard. Now paste. Move this new scrollbar to a location on your screen that makes sense. You could put it between columns E and G. Or, you can put on the right, as I have, to the right of column O.
Looking at your dashboard, select cell
G6. Set
G6 to reference the nation at the top of the list in the VLookup table. So,
G6 should have “
=H32” as its formula. Now drag
G6 down 14 rows. If you see one of your rows start showing a value of “0,” you’ve dragged too far. Now, while looking at your dashboard, select cell
O6. Map this cell reference the first weighted score on your VLookup table.
O6, then, should have the value “
=G32.” Drag down. The preset bar charts should show up automatically.
Now play with every scrollbar on the dashboard.
***
This is a good stopping point for Part 2. I might be working on a Part 3 as time allows. If I end up writing a Part 3, we’ll talk about how to make those bar charts. And, we’ll talk about how to build another graph that shows how each country performed in just one metric. Lastly, we’ll talk about how to populate the top and bottom rankings.
If you're really curious about the incell bar charts, you can see a good discussion on Chandoo's site,
How to Visualize Survey Results using Incell Panel Charts.
Questions? Feel free to ask.