ExcelDashboardWidgets

Helping you create stunning dashboards in Microsoft Excel including dials, speedometers, thermometers, and much more...

Upload and share your dashboard spreadsheets with other users here.

Moderator: DashboardWidgets

By psciandra
#243
Thanks I got the recent version..psciandra@omi.co does not exist which explains why I did not get. I am having trouble "linking" my dashboard. I have followed the directions but can not find clear directions on how to relink the the tabs within my sheet. I am attaching my dashboard for reference. Also can the daily trend be changed to monthly?
Attachments
(101.39 KiB) Downloaded 708 times
User avatar
By DashboardWidgets
#244
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.
ps11.jpg
ps_final.jpg
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..
ps1-a.jpg
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”.
ps1-b.jpg
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…
ps1-c.jpg
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 747 times
But your up/down widgets look like this (they have lost the title text and the % change text fields).
psi1.jpg
psi1.jpg (21.47 KiB) Viewed 5737 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”.
ps2.jpg
Ungrouping the widget (see image below) reveals the fields - some of which are blank – because they are not currently linked to cells with data…
ps3.jpg
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…
ps4.jpg
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
psi5.jpg (16.49 KiB) Viewed 5737 times
You’ll notice that when you finish the selection, the title text box will now be selected (see image below)!
ps6.jpg
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”).
ps7.jpg
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!).
ps8.jpg
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.
ps9.jpg
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…
ps10.jpg
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!

Download and try for free!