Chandoo,
Thanks for such a wonderful Excel resource! I am still a macro newbie, and definitely a dashboard newbie, but I'm excited at the prospect of becoming "more awesome!"
Anyway, here is a question for which I cannot seem to find a solution. My scrubbed data could be described as this: I need to track many departments that all carry different, but similar, products. Each of these products are separated into one of several "categories" for tracking. The products are different in each department, but in many cases products from one department can be in the same category as products from another department (think of the categories as positions in the lifecycle of the product, for example). Thus, a subset of the data can be summarized in the following manner:
Dept1_______Prod J______cat1[/code]
There are more details per product that are tracked, but you get the basic idea.
I already use macros on the main worksheet that allow me a one-button click to "filter in place" the complete data table to show any of the individual departments inventories and provide status to my boss on demand. I use countifs to generate an overall summary table of all data table entries, and sumproduct statements in conjunction with subtotal and offset to generate a different summary table of the visible data as it is filtered.
I also use macros to generate separate worksheets for each department that include a detailed inventory report on all the products they carry, including categories they are in. These are for reports that go to the individual departments. These macros are the same as the "filter in place" macro I use to generate the individual reports on demand; I just re-use them to filter the data into separate sheets so I don't have to do a lot of copying and pasting for each department's individual report.
This is all well and good, but your site has me thinking I could do better, perhaps with a dashboard? What I would like to show is something like this (I apologize for all the underscores; I didn't know how to format it correctly):
||_______Cat 1_____||_______Cat 2_____||_______Cat 3_____||[/code]
||__Dept__|_Prod___||__Dept__|__Prod__||__Dept__|__Prod__||[/code]
||________|________||________|________||________|________||[/code]
|| Dept 3 | Prod F || Dept 1 | Prod D || Dept 2 | Prod A ||[/code]
|| Dept 1 | Prod H || Dept 2 | Prod G || Dept 3 | Prod B ||[/code]
|| Dept 4 | Prod I ||________|________|| Dept 1 | Prod C ||[/code]
|| Dept 1 | Prod J ||________|________|| Dept 2 | Prod E ||[/code]
||________|________||________|________||________|________||[/code]
These are not just sums, but text, so I thought of arrays that I could then transpose to the columns. But they aren't static; e.g., the amounts of products in each category could change week to week and I don't want to display N/A or error in cells that are defined in the array but lack data. I thought of dynamic arrays, but I can't quite work out how to implement them. I thought of some form of VLookup or SumProduct, but again, I couldn't figure out how to implement them correctly.
Additionally, I would love to be able to filter (by checkbox or whatever) the above display by a single or more department(s) so that only the selected department(s) would show up and display all their products and what category they were currently in.
If that part is working, I believe it is a small enough detail to make companion charts and graphs that show dynamic numbers, such as how many products are in which category, etc., based again on the checkbox selection.
I am sure I am missing something basic.
Other ideas for the display of the above data are most welcome.
Thank you all in advance.
Mark
Thanks for such a wonderful Excel resource! I am still a macro newbie, and definitely a dashboard newbie, but I'm excited at the prospect of becoming "more awesome!"
Anyway, here is a question for which I cannot seem to find a solution. My scrubbed data could be described as this: I need to track many departments that all carry different, but similar, products. Each of these products are separated into one of several "categories" for tracking. The products are different in each department, but in many cases products from one department can be in the same category as products from another department (think of the categories as positions in the lifecycle of the product, for example). Thus, a subset of the data can be summarized in the following manner:
Code:
Department__Product_____Category
[code]Dept2_______Prod A______cat3
[code]Dept3_______Prod B______cat3
[code]Dept1_______Prod C______cat3
[code]Dept1_______Prod D______cat2
[code]Dept2_______Prod E______cat3
[code]Dept3_______Prod F______cat1
[code]Dept2_______Prod G______cat2
[code]Dept1_______Prod H______cat1
[code]Dept4_______Prod I______cat1Dept1_______Prod J______cat1[/code]
There are more details per product that are tracked, but you get the basic idea.
I already use macros on the main worksheet that allow me a one-button click to "filter in place" the complete data table to show any of the individual departments inventories and provide status to my boss on demand. I use countifs to generate an overall summary table of all data table entries, and sumproduct statements in conjunction with subtotal and offset to generate a different summary table of the visible data as it is filtered.
I also use macros to generate separate worksheets for each department that include a detailed inventory report on all the products they carry, including categories they are in. These are for reports that go to the individual departments. These macros are the same as the "filter in place" macro I use to generate the individual reports on demand; I just re-use them to filter the data into separate sheets so I don't have to do a lot of copying and pasting for each department's individual report.
This is all well and good, but your site has me thinking I could do better, perhaps with a dashboard? What I would like to show is something like this (I apologize for all the underscores; I didn't know how to format it correctly):
||_______Cat 1_____||_______Cat 2_____||_______Cat 3_____||[/code]
||__Dept__|_Prod___||__Dept__|__Prod__||__Dept__|__Prod__||[/code]
||________|________||________|________||________|________||[/code]
|| Dept 3 | Prod F || Dept 1 | Prod D || Dept 2 | Prod A ||[/code]
|| Dept 1 | Prod H || Dept 2 | Prod G || Dept 3 | Prod B ||[/code]
|| Dept 4 | Prod I ||________|________|| Dept 1 | Prod C ||[/code]
|| Dept 1 | Prod J ||________|________|| Dept 2 | Prod E ||[/code]
||________|________||________|________||________|________||[/code]
These are not just sums, but text, so I thought of arrays that I could then transpose to the columns. But they aren't static; e.g., the amounts of products in each category could change week to week and I don't want to display N/A or error in cells that are defined in the array but lack data. I thought of dynamic arrays, but I can't quite work out how to implement them. I thought of some form of VLookup or SumProduct, but again, I couldn't figure out how to implement them correctly.
Additionally, I would love to be able to filter (by checkbox or whatever) the above display by a single or more department(s) so that only the selected department(s) would show up and display all their products and what category they were currently in.
If that part is working, I believe it is a small enough detail to make companion charts and graphs that show dynamic numbers, such as how many products are in which category, etc., based again on the checkbox selection.
I am sure I am missing something basic.
Other ideas for the display of the above data are most welcome.
Thank you all in advance.
Mark
