Munjit has attended:
Excel Dashboards for Business Intelligence course
Dashboard in Excel
I was on the excel dashboard training course in January and I am trying to re-create the dashboard – Helpdesk efficiency. Although I have the solution I cannot see how the scroll bar formulas have been done using the ‘helper sheet’. As the numbers change in the helper sheet and I cannot see a formula. I remember there was a hidden formula in the actual dashboard sheet – but I cannot see it to replicate.
Scroll bar output: 1
Drop down output: 1
Maximum drop down: 711
Team drop down output: 4
Period drop down 1
Forecast Drop down 3
Many thanks for pointing me in the right direction.
RE: Dashboard in Excel
Hi Munjit,
Thank you for the forum question. I am glad that you summarize the course re-creating the Dashboard.
Sorry I can see there is an error in the Helper sheet
Scroll bar output: 1
Drop down output: 1
Maximum drop down: 31
Team drop down output: 4
Period drop down 1
Forecast Drop down 3
the formula in B3 in the helper sheet should be =counta('pivot agent'!A:A)-11
Right click the scroll bar and click FORMAT CONTROL and set:
MIN VALUE=1
MAX VALUE=31
INCREMENTAL CHANGE=1
PAGE CHANGE=10
CELL LINK= 'Helper sheet'!$B$1
In the Dashboard sheet B13 enter =OFFSET('pivot agent'!A4,'Helper sheet'!$B$1,0)
I hope this can help you if not please let me know.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Dashboard in Excel
Thank you Jens,
I have clicked on the scroll bar and on format control - but I do not know how to set the below using format control, as it only gives you options of 'Input Range' and 'Cell Link' - so where/how to I input MIN Value, MAX Value, Incremental Change, Page Change?
MIN VALUE=1
MAX VALUE=31
INCREMENTAL CHANGE=1
PAGE CHANGE=10
CELL LINK= 'Helper sheet'!$B$1
Thank you again
RE: Dashboard in Excel
Hi Munjit,
It sounds like you are using a wrong form control.
The scroll bar you will find on the Developer Tab in the controls group. Click Insert and the scroll bar is number 3 in the second row.
I hope this will help you and please let me know if you have more questions.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
