Hi Richard
Please take a quick look at the attached dashboard. You’ll notice we have linked your “My Dashboard”, “My Configuration Page” and “My Calculations Page” together for you and have put together a small explanation (below) regarding how we did it. Note that the "My Calculations" page of the version attached here is locked - I'll send you the unlocked version in a private message.
If you have any further questions – or require further clarification, please feel free to get back to us.
Best wishes
DashboardWidgets
----------------
How We Linked Together Your Dashboard
Let me talk you through how we linked your dashboard together step-by-step with an initial focus on the first row of up/down widgets..
Step #1
The first thing to do is to make sure that your three tabs (“My Dashboard”, “My Configuration Page”, and “My Calculations Page” are set up correctly).
I notice that you have opted for a 3 x 3 layout.
* The first row has 3 up/down widgets
* The second row has 3 trend widgets
* The third row has 3 dots widgets
Therefore we need to ensure that the “My Configuration Page” and the “My Calculations Page” also are set up with a 3 x 3 layout. You’ll notice on the “My Calculations Page” of your spreadsheet we have deleted the three funnel widget calculation tables (there are no funnel widgets in the dashboard) and have inserted three calculation tables for the trend widgets (which was missing).
The revised “My Calculations Page” should now look a little like this..
The next step is to link the “My Calculations Page” to the “My Configuration Page”. To do this, you need to look closely how the original “Widget Showcase” Tab of the Dashboard Widgets Spreadsheet is linked together and copy the same linkages.
Step #2
Let’s start with cell “E5” on your new dashboard’s “My Calculations Page”.
This cell is currently linked to the old spreadsheet configuration page for the up/down widget “C:\Users\Paul\Documents\OMI\Operational\blah\blah\blah”.
We need to connect it to our new dashboard’s current value on the “My Configuration Page”. To do this carry out the following steps:
3a. With the “My Calculations Page” cell E5 selected, click on the formula bar at the top of the page.
3b. Delete the existing link.
3c. Type “=” into the formula bar.
3d. Use the mouse to select the “My Configuration Page” tab at the bottom of the page.
3e. Click on cell E6 (containing the up/down widget value) and press “ENTER”.
In a similar way, let’s update the “Previous Value” cell as follows..
3f. With the “My Calculations Page” cell E7 selected, click on the formula bar at the top of the page.
3g. Delete the existing link.
3h. Type “=” into the formula bar.
3i. Use the mouse to select the “My Configuration Page” tab at the bottom of the page.
3j. Click on cell E8 (containing the up/down widget previous value) and press “ENTER”.
The “My Calculations Page” should now look something like this…
You’ll also notice that the rest of the cells have now automatically updated themselves (for example the up/down indicator in cell E9 is pointing up because the current monthly billings are higher than the previous month’s billings).
Now we need to link these calculation and configuration cells to our dashboard!!
Step #3
You’ll notice on the “My Dashboard” page that the “up/down widgets” in the top row have lost their data sources in the copy/paste step.
The up/down widget should look like this..
(100.65 KiB) Downloaded 922 times
But your up/down widgets look like this (they have lost the title text and the % change text fields).
psi1.jpg (21.47 KiB) Viewed 6974 times
If you right click the top-right edge of the widget box (to select it) and then select “ungroup” (see image below) you’ll notice that all of the text-boxes are still there – they just are not linked to cells on the “My Configuration Page”.
Ungrouping the widget (see image below) reveals the fields - some of which are blank – because they are not currently linked to cells with data…
Step #4
The next step involves linking each of these 4 fields to their relevant data cells on the “My Configuration Page”. The easiest way to select these fields (in order to link them to a data cell) is to use the “select objects” tool which can be found on the ribbon as follows HOME-> FIND AND SELECT -> SELECT OBJECTS…
Using the “select objects” tool select the first title text box. Due to the complex overlapping configuration, the best way to do this is to draw a selection rectangle starting in the top-left corner of the widget (just outside of the background so you don’t end up selecting the background box) and down to mid-way along the right hand side edge (see image below).
psi5.jpg (16.49 KiB) Viewed 6974 times
You’ll notice that when you finish the selection, the title text box will now be selected (see image below)!
You should also notice that in the formula bar, it is telling us that this textbox is linked to the data that is in cell $F$124 (which doesn’t have any content – hence the title is blank!)
Step #5
The next step is to change the link this text box to cell E4 on the “My Configuration Page” (which holds the title of the widget). The easiest way to do this is with the following steps..
5a. With the texbox selected, click on the formula bar at the top of the page.
5b. Delete the existing link to cell “$F$124”.
5c. Type “=” into the formula bar.
5d. Use the mouse to select the “My Configuration Page” tab at the bottom of the page.
5e. Click on cell E4 (containing the title text) and press “ENTER”.
The dashboard should now look like the image below (notice that the text box is now linked to the cell contents on the “My Configuration Page”).
Step #6
Our next minor problem is that the font size has been reverted back to the default font settings.
To correct this, change the font-size back to 24 pts and also change the style back to BOLD.
Step #7
We now need to follow roughly the same process to link the other fields on the up/down widget to their correct data sources (following the pattern in the “Widgets Showcase” of the original “Dashboard Widgets Spreadsheet”).
Let’s link the “value” text box which currently says “805,918 USD$”. Clicking on the textbox reveals the data source in the formula box at the top of the page. As you can see, this value is still linked to the old spreadsheet (it is linked to an external data source!).
To link this text box to the current spreadsheet we follow the same procedure as before.
7a. With the texbox selected, click on the formula bar at the top of the page.
7b. Delete the existing link to the cell (in the external spreadsheet).
7c. Type “=” into the formula bar.
7d. Use the mouse to select the “My Calculations Page” tab at the bottom of the page (note this value is not on the “My Configuration Page”.
7e. Click on cell E6 (containing the widget value) and press “ENTER”.
The dashboard should now look like the image below.
Step #8
You’ll notice the value is now correctly linked to the value provided in the “My Calculations Page” but Excel has reverted back to the default font settings. To correct this, change the font-size back to 55 pts and also change the style back to BOLD.
Step #9
We now need to repeat the process for the up/down widget (the text box needs to be linked to cell “E9” on the “My Calculations Page”) and the % change text box (the text box needs to be linked to the cell “E10” on the “My Configuration Page”). Once these text boxes have been linked (and resized back to their original sizes – recall that Excel reverts to the default settings), your up/down widgets should look like this…
Now, any monthly updates you make on the “My Configuration Page” will automatically update the dashboard.
Step #10
Repeat the process for the rest of the widgets. The key is to follow the example of how the widgets in the “Widgets Showcase” are linked together. If you have more questions or require more clarification feel free to get back to us!
