• 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.

Excel Dashboard using DropDown Boxes

KristyLee0228

KristyLee0228

Member
I have attached a sample file of what I am looking to create. I have the Named Ranges completed.

I am trying to create a dashboard using dropdown boxes at the top. A little similar to a pivot table, but it looks better.

I am having a hard time creating the formulas for the table.
If there is any other data or information needed to help with my request, please let me know.
Thank you in advance for your help.
 

Attachments

  • Sample Data 6.9.15.xlsx
    427.6 KB · Views: 35
Hi Kristy,

The look you want is structured as a pivot table. As a matter of fact, this data structure is really optimal for pivot. In my humble opinion, I would try to do it as pivot first and play around with the design and layout format before you jump into dash boarding, which is not necessarily optimal for all data types.

I did a quick pivot and found it to be exactly what you need. If you need help, please provide me with the explanation of the headers below and I will help.

upload_2015-6-9_16-43-50.png
 
KristyLee0228 said:
I have attached a sample file of what I am looking to create. I have the Named Ranges completed.

I am trying to create a dashboard using dropdown boxes at the top. A little similar to a pivot table, but it looks better.

I am having a hard time creating the formulas for the table.
If there is any other data or information needed to help with my request, please let me know.
Thank you in advance for your help.
Click to expand...
Kindly check and revert me back.
 

Attachments

  • Sample Data 6.9.15.xlsx
    425.2 KB · Views: 14
I thank you for your help and your response, but I don't see how what you sent back is any different than the file that I originally attached.
 
ysherriff said:
Hi Kristy,

The look you want is structured as a pivot table. As a matter of fact, this data structure is really optimal for pivot. In my humble opinion, I would try to do it as pivot first and play around with the design and layout format before you jump into dash boarding, which is not necessarily optimal for all data types.

I did a quick pivot and found it to be exactly what you need. If you need help, please provide me with the explanation of the headers below and I will help.

View attachment 19685
Click to expand...
The headers are from Column N on the Data Tab labeled - 'Result'.
Thank you for your help and your response!
 
Narayan,
The values of the range C7 through S11 are found on the data tab within Column N. The count of each account with that specific Result Code.
Thank you!
 
Hopefully this is what you want. Please check all formulas. Instruction to update sheets are in file.
 

Attachments

  • DASH.xlsx
    446.9 KB · Views: 40
chirayu said:
Hopefully this is what you want. Please check all formulas. Instruction to update sheets are in file.
Click to expand...

Oh how I thank you so very much!
This is absolutely perfect! If I have any other questions, I'll email you back.
I am updating with current data now!
 
chirayu said:
Hopefully this is what you want. Please check all formulas. Instruction to update sheets are in file.
Click to expand...

I have one question..... Is there a way to include in the drop down boxes an option to select all and view All Supervisors and All Dates to look at the company as a whole?

I thank you so very much for your help!
 
Attached. Please check formulas. Instructions updated.
 

Attachments

  • DASHv2.xlsx
    491.2 KB · Views: 9
chirayu said:
Attached. Please check formulas. Instructions updated.
Click to expand...

Hi Chirayu!!
Again, I thank you so very much for your help with this!! It is very appreciated!!

I just have one question... when I select the drop down box for RMS Code, none of the numbers change. Is there a separate formula needed to add?
How can we get this to change per selection?

Thank you again so very much!!
 
I don't mind. I absolutely appreciate your help. I am looking at the formulas and also trying to get the option to select ALL for the codes.
If I can get your help with that, that would be greatly appreciated.
thanks again!!!
 
chirayu said:
Sorry for late reply. Please check. Changes made.
Click to expand...

I only need your help with 2 other things.... I am sorry to be a bother. I tried getting the formula to updated when you select ALL CODES, but I could not figure it out.

I'd also like to put a total row in the bottom of the main sheet. And for this row to update when you make selections. IS that at all possible?

Thanks again, Chirayu!!!!!
 
Hi. Looking at the file again I realized its better for you to create a pivot rather than use the file I made.

The level of bifurcation given in the formulas is not enough & adding more would make the file heavy because there are multiple scenarios.

The formulas I made are just made as per previous requirement but for full functionality the way you want is going to take a long time to make.

Code: 
'Level of Bifurcation required for Dashboard

CallDate  | Supervisor| RMSCode
===============================
ALL       | ALL       | ALL
ALL       | ALL       | SPECIFIC
ALL       | SPECIFIC  | ALL
SPECIFIC  | ALL       | ALL
SPECIFIC  | SPECIFIC  | ALL
ALL       | SPECIFIC  | SPECIFIC
SPECIFIC  | SPECIFIC  | SPECIFIC
 
See attached. I've created a couple of dashboards
1. With filters the way you wanted
2. With slicers as filters

Not fully sure if the results are accurate so do check and get back if there are any problems with the results.
 

Attachments

  • Sample Data 6.9.15_VD.xlsx
    886.5 KB · Views: 18
I've created a sample pivot file basis the criteria that I spoke about earlier. Had to move the Hours worked column around though as can't add it in pivot. So added some conditional formats to make it look like the whole thing is a pivot. Formulas are till row 500. Sheet Password is 1
 

Attachments

  • DASHv4.xlsx
    680.5 KB · Views: 15
Vivek D said:
See attached. I've created a couple of dashboards
1. With filters the way you wanted
2. With slicers as filters

Not fully sure if the results are accurate so do check and get back if there are any problems with the results.
Click to expand...

Vivek D,
This looks awesome so far. I never got notification of this message so I am just checking it now. Thank you so much for the response and the help!! The only thing I want to change at this point is RMS code. And change it to Client Id.
I am going to play with this a bit but if you could help me with that small change, that would be great!

Thank you again!!!
 
See attached. I've added Client ID in both the Dashboard options.

Which one did you prefer... 1st or 2nd?
 

Attachments

  • Sample Data 7.31.15_VD.xlsx
    888 KB · Views: 12
Vivek D said:
See attached. I've added Client ID in both the Dashboard options.

Which one did you prefer... 1st or 2nd?
Click to expand...


I love the 2nd Dashboard. I am so happy with this! thank you so very much!
I only have two questions: First, when I update the data, the old dates stay in the CallDate box, but are greyed out. Is there a way to remove the old dates from there completely?

Second, can there be a totals row added to the bottom?

Again, I thank you so very much for your help!!!
Greatly appreciated!
 
Haven't been checking Chandoo lately as I've been busy with work.

Use attached. If you are still having the date problems send me the file with the updated data.

I've added the Totals up top as I thought that would be better than having to scroll down to see the total. It was also easier to do so went that route :).
 

Attachments

  • Sample Data 8.0.15_VD.xlsx
    888.8 KB · Views: 45
You must log in or register to reply here.
Back
Top