• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create dynamic Dashboard

A

Abhishek_8895

New Member
I want to build a a dashboard that has a list of metrics as a drop-down and when from the drop down a metric is selected, it pulls the value of that metric from the sheet where the data is stored and updates automatically. Attached is the excel file and appreciate some guidance.
Cluster performance dashboard is the sheet where I want the automation to work and the remaining are the metrics sheet where data will be added manually...
 

Attachments

  • Service Level Management.xlsx
    44.9 KB · Views: 10
In the attached, a ridiculous formula in sheet Cluster Performance Dashboard, cell H7, which can be copied down and across.
As a test, in sheet NRC, a formula in cell G9 puts numbers in the cells. In the Cluster Performance Dashboard sheet, if you choose, Non Return Charges - Lease period > 36 months in cell D6's dropdown, you should see the same numbers in the grid.
The formula in cell H7 could have been simpler but this way, should the dates or the areas in the various tables change the results should remain correct.
 

Attachments

  • Chandoo55898Service Level Management.xlsx
    53.7 KB · Views: 8
p45cal said:
In the attached, a ridiculous formula in sheet Cluster Performance Dashboard, cell H7, which can be copied down and across.
As a test, in sheet NRC, a formula in cell G9 puts numbers in the cells. In the Cluster Performance Dashboard sheet, if you choose, Non Return Charges - Lease period > 36 months in cell D6's dropdown, you should see the same numbers in the grid.
The formula in cell H7 could have been simpler but this way, should the dates or the areas in the various tables change the results should remain correct.
Click to expand...
Thank you so much!!

The solution is exactly how, I wanted to design it.
However, there is one sheet that was hidden and the name is Performance Dashboard, I wanted to extract the metric wise grand total column month wise in that sheet for a Bird-eye view, how can we accomplish that?

I tried to use the formula in the other sheets, but it was quiet hard for me to get it.
Appreciate some extended support, if possible.
 
Abhishek_8895 said:
Thank you so much!!

The solution is exactly how, I wanted to design it.
However, there is one sheet that was hidden and the name is Performance Dashboard, I wanted to extract the metric wise grand total column month wise in that sheet for a Bird-eye view, how can we accomplish that?

I tried to use the formula in the other sheets, but it was quiet hard for me to get it.
Appreciate some extended support, if possible.
Click to expand...
Oh, I tried further and seems I got it... but let me know if there is any other way you could recommend.

I used: =XLOOKUP($D$6,'New Joiner'!$C$8,'New Joiner'!G$20,"",0)
 
When I discovered you also had a hidden sheet called Grid I noticed you had a list of service levels that you used in data validation, so I thought I'd extend its use.
I made it into a proper table and added a column of sheet names. This will serve as a way of translating service levels to sheet names, so I called the table ServiceLevelToSheetName. This means I can simplify the formula on the Cluster Performance Dashboard sheet cell H7 to:
Code: 
=LET(a,INDIRECT("'" & VLOOKUP($D$6,ServiceLevelToSheetName,2,0) & "'!F7:R20"),b,INDEX(a,MATCH($G7,TAKE(a,,1),0),MATCH(H$5,TAKE(a,1),0)),IF(b=0,"",b))
which is considerably shorter and easier to maintain.

I also used this table in cell G6 of the sheet Performance Dashboard, which can be copied across and down:
Code: 
=LET(sname,VLOOKUP($D6,ServiceLevelToSheetName,2,0),a,INDEX(INDIRECT("'" & sname & "'!G20:R20"),,MATCH(G$5,INDIRECT("'" & sname & "'!G7:R7"),0)),IF(a=0,"",a))

All this in the attached.
 

Attachments

  • Chandoo55898Service Level Management_v2.xlsx
    56.5 KB · Views: 15
p45cal said:
When I discovered you also had a hidden sheet called Grid I noticed you had a list of service levels that you used in data validation, so I thought I'd extend its use.
I made it into a proper table and added a column of sheet names. This will serve as a way of translating service levels to sheet names, so I called the table ServiceLevelToSheetName. This means I can simplify the formula on the Cluster Performance Dashboard sheet cell H7 to:
Code: 
=LET(a,INDIRECT("'" & VLOOKUP($D$6,ServiceLevelToSheetName,2,0) & "'!F7:R20"),b,INDEX(a,MATCH($G7,TAKE(a,,1),0),MATCH(H$5,TAKE(a,1),0)),IF(b=0,"",b))
which is considerably shorter and easier to maintain.

I also used this table in cell G6 of the sheet Performance Dashboard, which can be copied across and down:
Code: 
=LET(sname,VLOOKUP($D6,ServiceLevelToSheetName,2,0),a,INDEX(INDIRECT("'" & sname & "'!G20:R20"),,MATCH(G$5,INDIRECT("'" & sname & "'!G7:R7"),0)),IF(a=0,"",a))

All this in the attached.
Click to expand...
Thank you so much! Its easier and clear to me...
The issue is resolved and thanks experts
 
One last thing, if I want to protect only one specific row or column of a sheet, is there a way to do it? Or it's the basic protect sheet and workbook rule?
 
Yes, protect the sheet (I don't think you need to protect the workbook).
Before protecting the sheet, in the Format Cells dialogue, in the Protection tab, unlock all the cells in the range you're using on the sheet. Then select only the column(s) you want to protect, Format Cells again and lock the cells (then protect the sheet).
 
p45cal said:
Yes, protect the sheet (I don't think you need to protect the workbook).
Before protecting the sheet, in the Format Cells dialogue, in the Protection tab, unlock all the cells in the range you're using on the sheet. Then select only the column(s) you want to protect, Format Cells again and lock the cells (then protect the sheet).
Click to expand...
Thanks, I used edit range and was successful
 
You must log in or register to reply here.
Back
Top