Dashboard - Table Scroll Sort addition

[rjacmuto32]

I was reading the dashboard section you have on the table scrolling and sorting. It it awesome and will work for something I doing. I do have a question about is it possible ot add something and how to do it.


I used your stock table on your website here

http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/


I bascially added a cell so next to the Product Name #, I display another field called Bucket. Basically that field is either populated with Screws, Tools, Lumber, etc. Is there a way to add a filter and keep the scrolling and sorting? Basically I'd like to say see all my data sorted by kpi 1 or 2, but have the option to filter it so I see only say Screws, and still have the sorting by kpi 1 and 2 etc?


Thanks,

--Robert

 

[Mike86]

Simple answer is "yes". When you do the unique values, add a check for your filter value. Something like:


=IF(B2=J$1,(C2+A2/100000),"")


With the B col as the filter field, J1 as the selected filter, c the kpi column, and A as the key field. When you do the sort using the Large function per the sited example, the rows matching filter will display values and be sorted.


When you do the scrolling table, you'll need to adjust the end point to equal the number of rows matching your filter.


Flipping between Screws, Tools, etc, will make the field containing the filter redundant in the scrolling table. If you've already selected Screws, having every line say "Screws" isn't helping. I'm not sure how to change the number of displayed columns in the scrolling table, but maybe someone else could help out.

 

[rjacmuto32]

where would you put that formula? On the Calculation tab?


Still stuck on this.


Or would anyone know a better way to maybe put a filter at the top of the scrolling table to only show say Screws, instead of seeing it on every row?

 

[rjacmuto32]

Here is the file where I added in the field. I want to have a filter on so I can only select Nut and still have the sorting work.


https://skydrive.live.com/redir.aspx?cid=05ea03be521b84f8&resid=5EA03BE521B84F8!121&authkey=7zr4SRj10Kc%24


Any suggestions?

 

[Busymanjohn]

Hi rjacmuto32 ,,,, sounds to me like you are looking for a drop down list to enable you to view only certain data within your Dashboard ( or part of your Dashboard ) ,.. am I right ? BTW, Skydrive looks to be down, can't access the file

 

[rjacmuto32]

Correct, a dropdown list to view only certain data.

 

[Robapottamus]

Hello - I have a similar problem that I am working with. Robert, did you find a solution in the end?


I am not sure if it is appropriate to post an extension question on an existing post so please advise if I should start a new one...


For application to my particular problem i want to have a drop down filter (more likely a number of check boxes) so I can filter my Dashboard for one OR MORE criteria, often not one only. Does anyone have a general suggestion of how this could be applied? I am fine with the drop down box method for filtering for one type of product name, say screws,(using Robert's example above) - But what if I wanted to filter for screws and tools?


The hang up for me at the moment is that I am not sure if I can can have a multiple select option on a drop down filter (preferrable), as I have 33 options in my list and therefore 33 check boxes sitting next to my dashboard would be quite messy, and almost look a bit silly as my Dashboard is only 15 rows long.


Your help is appreciated

Rob