9 Box Talent Mapping Template - Adding "All" to the Department Name Filter

[Trashman]

Hello - I downloaded the 9 Box Talent Mapping Template and have been modifying it for my use. It is a great template that I am going to use to see how my department's performance reviews and distributed.

I would really like to use the filter - similar to Department Name on the Grid Output (Excel 365) tab. However, for it to be really useful, it would be great if one of the selections was "All" so you could see all Departments together in addition to being able to break it down.

This functionality would make it great to use across any number of categories and still dynamically map names.

I have been trying to figure this out for a week now and I can't seem to make it work.

Any suggestions?

 

[AliGW]

No sample workbook attached, so here's a DIY option in the hope that you have Excel 365 (you don't say):

 

[Debaser]

Change L4 on Data Entry to:
=VSTACK("All",SORT(UNIQUE(talent[Department])))

then change the formula in E5 on the Output sheet to:
=IFERROR(TEXTJOIN(","&CHAR(10),TRUE, SORT(FILTER(talent[Candidate],(talent[Performance]=E$8)*(talent[Potential]=$D5)*((talent[Department]=$G$3)+($G$3="All"))))),"")

then copy across and down.

 

[Trashman]

This is Awesome Debaser. Works Great!!!!! Now i can add more “filters” based on region, etc. this is a lot of help.

2nd question: is there a way to do an dynamic analysis (with the department selection changing) with the count of the number of names in each box?

 

[Debaser]

For a count, you could use something like:

=IFERROR(ROWS(FILTER(talent[Candidate],(talent[Performance]=E$8)*(talent[Potential]=$D5)*((talent[Department]=$G$3)+($G$3="All")))),0)