- Mon Jul 06, 2015 10:29 pm
#277
Dear Dashboarders,
Here’s a little guidance on duplicating the speedo dials including how to configure the needle.
Hope it helps you set up your own dashboard.
If you have problems, feel free to upload your dashboard, we’ll link it together and send it back to you.
Warm regards
ExcelDashboardWidgets
Duplicating the Speedo Dial Widget Tutorial
Step #1
Create a new spreadsheet with three tabs as follows..
1. My Dashboard Page
2. My Configuration Page
3. My Calculations Page
Step #2
Select the speedo widget from the ExcelDashboardWidgets spreadsheet ‘Widgets Showcase’ Tab and copy onto your new spreadsheet ‘My Dashboard’ Tab three times.
Your new dashboard should look something like this…
Step #3
Select the configuration table from the ExcelDashboardWidgets “Widgets Showcase” tab 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 reformatted the cell sizes a bit so we can read the text)…
Now we are ready to start adding some data to our dashboard…
Step #4
On the “My Configuration Page”, start to enter the titles, units, actual dial value and dial ranges that you want to show. We’re using the following configuration for our example..
Dial #1: Weekly Customer Service Emails Received [Value: 43.2]
Dial #2: Answered Within One Working Day [Value: 28.3]
Dial #3: Answered Within Three Working Days [Value: 12.5]
Units: x 1,000 Emails / Week
Scales: 0 – 50
Now we need to add some content to the “My Calculations Page” that will create the dial needle and gauge scales.
Step #5
Select the calculation table ‘A – Linear Dial Widget’ from the ExcelDashboardWidgets “Widgets Showcase Calcs” tab and copy onto your new spreadsheet ‘My Calculations Page’ Tab three times.
Your “My Calculations Page” should look a little like this..
The next step is to link the yellow squares on the “My Calculations Page” to the “My Configuration Page”. Following this, the blue squares will automatically create the Speedo Widget needle and scale intervals.
Step #6
We will link the yellow squares to the “My Configuration Page” with the following steps..
6a) Select the “Min Value” cell for the first dial.
6b) In the formula bar type “=”.
6c) Using the mouse select the “My Configuration Page” tab.
6d) Select the “Minimum Value” cell for the first dial.
6e) Press “Enter”.
Repeat the process for the remaining yellow squares on the “My Calculations Page”.
The final stage is to link the widgets on the “My Dashboard Page” to the “My Configuration Page” and “My Calculations Page”
Step #7
Go to the “My Dashboard Page”.
Open the “Selection Pane” from the ribbon (HOME > EDITING > FIND & SELECT > SELECTION PAGE). See image below..
With the selection pane open, you will now see all of the text box and chart elements that make up the Speedo Dial widgets. See image below..
Our main task now is to link the following widget elements to the correct cells on the “My Configuration Page” and “My Calculations Page”..
- Linear Dial Title
- Linear Dial Main Value
- Linear Dial Needle
- Linear Dial Units
- Linear Dial Scale Values
Let’s start with something relatively easy. In the selection pane click all of the minus (-) signs so you just end up with the three widgets as follows..
I would recommend renaming these as follows...
Linear Dial #1
Linear Dial #2
Linear Dial #3
You can also re-order the widgets using the up and down buttons at the bottom of the selection pane (currently they are listed in the order in which we pasted them onto the sheet).
Choose the Linear Dial #1 Title from the selection pane. You’ll notice in the formula bar that the text box is currently linked to cell “$F9” – however, we want to link it to our configuration page.
To link the Linear Dial #1 Title text box carry out the follow steps (with the Linear Dial #1 Title text box selected)...
7a) Delete the current contents of the formula bar.
7b) Type equals “=”.
7c) Using the mouse, select the “My Configuration Page” tab.
7d) Select the dial #1 title.
7e) Press ENTER.
You’ll notice that the font size has gone back to the default setting so change it to SIZE 24pt BOLD.
Step #8
Repeat the process for Linear Dial Main Value as follows..
8a) Select the Linear Dial #1 Main Value in the SELECTION PANE.
8b) Delete the current contents of the formula bar.
8c) Type equals “=”.
8d) Using the mouse, select the “My Calculations Page” tab.
8e) Select the Dial #1 Actual Value.
8e) Press ENTER.
Back on the “My Dashboard Page” you’ll notice that again, the formatting of the text box has gone back to the default setting. Change this back to FONT SIZE 22pt and BOLD and WHITE COLOR.
Repeat this process for the scale values, linking them to the scale values in the “My Calculations Page”
The last widget element to configure is the Speedo Dial Needle..
Step #9
To configure the Speedo Dial Needle, we need to temporarily ungroup the widget elements. To do this, select the top right hand corner of the widget, right click and choose “GROUP > UNGROUP”. See image below..
You’ll notice that the Speedo Dial Widget has now ungrouped itself and you can see all of the different parts selected.
Now select a cell anywhere on the worksheet (to deselect the Speedo Dial elements) then select the Speedo Needle and you’ll see the old charting formula in the formula bar. See image below..
To link this Speedo Needle to our “My Calculations Page” we need to do the following...
9a) Select the formula box.
9b) Replace the link to the external spreadsheet inside the single quotes ‘[ExcelDashboardWidgets Spreadsheet5 - Unlocked.xlsx]Widget Showcase Calcs' with a link to the current “My Configuration Page”. In my case, the final formula is as follows..
Step #10
Finally, we need to group up all of the elements back together into one single widget. To do this, select the top right hand corner of the widget, then right-click and select “GROUP > REGROUP”.
Step 11
Now repeat the process for the other two Linear Dial Widgets!
Your final dashboard should look a little like this!!
Please note that the unlocked version of this dashboard can be downloaded in the Commercial License Holder's Download Area here.. http://www.exceldashboardwidgets.com/ph ... ?f=25&t=90
If any of these steps aren’t clear – please feel free to let us know.
Also feel free to attach your dashboard and we’ll have link it together for you and send it back!
Good luck with your dashboard project.
DashboardWidgets
Here’s a little guidance on duplicating the speedo dials including how to configure the needle.
Hope it helps you set up your own dashboard.
If you have problems, feel free to upload your dashboard, we’ll link it together and send it back to you.
Warm regards
ExcelDashboardWidgets
Duplicating the Speedo Dial Widget Tutorial
Step #1
Create a new spreadsheet with three tabs as follows..
1. My Dashboard Page
2. My Configuration Page
3. My Calculations Page
Step #2
Select the speedo widget from the ExcelDashboardWidgets spreadsheet ‘Widgets Showcase’ Tab and copy onto your new spreadsheet ‘My Dashboard’ Tab three times.
Your new dashboard should look something like this…
Step #3
Select the configuration table from the ExcelDashboardWidgets “Widgets Showcase” tab 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 reformatted the cell sizes a bit so we can read the text)…
Step #4
On the “My Configuration Page”, start to enter the titles, units, actual dial value and dial ranges that you want to show. We’re using the following configuration for our example..
Dial #1: Weekly Customer Service Emails Received [Value: 43.2]
Dial #2: Answered Within One Working Day [Value: 28.3]
Dial #3: Answered Within Three Working Days [Value: 12.5]
Units: x 1,000 Emails / Week
Scales: 0 – 50
Now we need to add some content to the “My Calculations Page” that will create the dial needle and gauge scales.
Step #5
Select the calculation table ‘A – Linear Dial Widget’ from the ExcelDashboardWidgets “Widgets Showcase Calcs” tab and copy onto your new spreadsheet ‘My Calculations Page’ Tab three times.
Your “My Calculations Page” should look a little like this..
Step #6
We will link the yellow squares to the “My Configuration Page” with the following steps..
6a) Select the “Min Value” cell for the first dial.
6b) In the formula bar type “=”.
6c) Using the mouse select the “My Configuration Page” tab.
6d) Select the “Minimum Value” cell for the first dial.
6e) Press “Enter”.
Repeat the process for the remaining yellow squares on the “My Calculations Page”.
The final stage is to link the widgets on the “My Dashboard Page” to the “My Configuration Page” and “My Calculations Page”
Step #7
Go to the “My Dashboard Page”.
Open the “Selection Pane” from the ribbon (HOME > EDITING > FIND & SELECT > SELECTION PAGE). See image below..
- Linear Dial Title
- Linear Dial Main Value
- Linear Dial Needle
- Linear Dial Units
- Linear Dial Scale Values
Let’s start with something relatively easy. In the selection pane click all of the minus (-) signs so you just end up with the three widgets as follows..
Linear Dial #1
Linear Dial #2
Linear Dial #3
You can also re-order the widgets using the up and down buttons at the bottom of the selection pane (currently they are listed in the order in which we pasted them onto the sheet).
Choose the Linear Dial #1 Title from the selection pane. You’ll notice in the formula bar that the text box is currently linked to cell “$F9” – however, we want to link it to our configuration page.
7a) Delete the current contents of the formula bar.
7b) Type equals “=”.
7c) Using the mouse, select the “My Configuration Page” tab.
7d) Select the dial #1 title.
7e) Press ENTER.
You’ll notice that the font size has gone back to the default setting so change it to SIZE 24pt BOLD.
Step #8
Repeat the process for Linear Dial Main Value as follows..
8a) Select the Linear Dial #1 Main Value in the SELECTION PANE.
8b) Delete the current contents of the formula bar.
8c) Type equals “=”.
8d) Using the mouse, select the “My Calculations Page” tab.
8e) Select the Dial #1 Actual Value.
8e) Press ENTER.
Back on the “My Dashboard Page” you’ll notice that again, the formatting of the text box has gone back to the default setting. Change this back to FONT SIZE 22pt and BOLD and WHITE COLOR.
Repeat this process for the scale values, linking them to the scale values in the “My Calculations Page”
The last widget element to configure is the Speedo Dial Needle..
Step #9
To configure the Speedo Dial Needle, we need to temporarily ungroup the widget elements. To do this, select the top right hand corner of the widget, right click and choose “GROUP > UNGROUP”. See image below..
Now select a cell anywhere on the worksheet (to deselect the Speedo Dial elements) then select the Speedo Needle and you’ll see the old charting formula in the formula bar. See image below..
9a) Select the formula box.
9b) Replace the link to the external spreadsheet inside the single quotes ‘[ExcelDashboardWidgets Spreadsheet5 - Unlocked.xlsx]Widget Showcase Calcs' with a link to the current “My Configuration Page”. In my case, the final formula is as follows..
Code: Select all
You’ll notice in the image below that my Speedo Needle data can be found on the “My Calculations Page” tab in cells G7 to G11 and H7 to H11 those are the cells I need to put in the formula. See image below.=SERIES('My Calculations Page'!$H$6,'My Calculations Page'!$G$7:$G$11,'My Calculations Page'!$H$7:$H$11,1)Finally, we need to group up all of the elements back together into one single widget. To do this, select the top right hand corner of the widget, then right-click and select “GROUP > REGROUP”.
Step 11
Now repeat the process for the other two Linear Dial Widgets!
Your final dashboard should look a little like this!!
If any of these steps aren’t clear – please feel free to let us know.
Also feel free to attach your dashboard and we’ll have link it together for you and send it back!
Good luck with your dashboard project.
DashboardWidgets
