As promised, here is a quick Duplicating Widgets Tutorial using the unlocked widget from the previous post.
Step #1
Create a new Excel spreadsheet with three tabs as follows..
1. My Dashboard Page
2. My Configuration Page
3. My Calculations Page
Step #2
Select the up/down widget from the old spreadsheet ‘Widget Duplication Exercise’ Tab and copy onto your new spreadsheet ‘My Dashboard’ Tab three times.
Your new excel dashboard should look something like this…
Step #3
Select the configuration table from the excel dashboard widgets spreadsheet and copy onto your new spreadsheet ‘My Configuration Page’ Tab three times.
Your new ‘My Configuration Page’ should look something like this (note that I stretched the cells a bit so we can read the text)…
Step #4
Select the calculation table from the old spreadsheet and copy onto your new spreadsheet ‘My Calculations Page’ Tab three times.
Your new ‘My Calculations Page’ should look something like this (note that I stretched the cells a bit so we can read the text)…
Step #5
Go to the “My Configuration Page” and change the titles of the three configuration tables as follows..
1. Monthly Website Visitors
2. Monthly New Website Visitors
3. Monthly Existing Website Visitors
Enter six values into the boxes entitled ‘widget value’ and ‘previous value’ (the value from the previous month).
Step #6
Go to the “My Calculations Page”. Select the first widget VALUE cell (you’ll notice in the formula box it says “=#REF!”) and link it to the “My Configuration Page” first widget value. The best way to do this is as follows..
1. On the “My Calculations Page” select the first widget VALUE cell.
2. Type “=” into the formula bar.
3. Use your mouse to select the “My Configuration Page”.
4. Select the first widget VALUE box.
5. Press enter.
Repeat the exercise for the PREVIOUS VALUE cell.
Repeat the exercise for the second and third widgets calculation tables.
Press F9 to update the excel spreadsheet and you’ll notice that the PERCENT and UP/DOWN cells will now update automatically.
Now comes the fun part – the “My Dashboard Page” is now ready for building!
Step #7
Go to the “My Dashboard” tab (which should have the three unlinked up/down widgets).
On the ribbon, select “FIND & SELECT > SELECTION PANE”.
Your screen should look a little like this…
In the selection pane you should see the elements that make up the three up/down widgets.
You may find it helpful to rename the widget elements #1, #2, and #3 as follows…
Step 8
Follow these steps to link the text boxes to their correct data sources.
1. Select “Up / Down Widget #1 Title” in the selection page on the right hand side.
2. In the formula box type “=”.
3. Using the mouse click the “My Calculations Page” tab.
4. Click the widget #1 VALUE cell.
5. Press enter.
Your excel dashboard should look like the one below…
IMPORTANT NOTE: This process always seems to revert the textbox back to its default text font size (typically Arial 10). You will need to manually change the font size back to the settings you like!
Step 9
Repeat Step #8 and link the rest of the text boxes in the RHS selection pane to their correct data sources.
Your final dashboard should look like this..
You can download this final dashboard here….
(16.39 KiB) Downloaded 646 times
If you are really adventurous, you could add a dropdown selection box (to choose the month) which automatically looks up the values for the month in a very simple database (without using any macros). There is an example dashboard that does this in the post here…
http://www.exceldashboardwidgets.com/ph ... p?f=9&t=14
Please feel free to get in touch if there is anything that is not clear.
Good luck with your excel dashboard project.
DashboardWidgets
