Yvonne Love
Member
I'm not even sure how to go about looking for this. I am trying to create individual charts for each attorney in our office. I'm set on most everything, but I am trying to create a chart comparing 2014 and 2015 hours for each attorney's top 10 clients in either 2014 or 2015. However, the number of clients will vary depending on each attorney, so I'm not sure how to create a dynamic list that just shows the client with data (my formulas are rightfully resulting in #N/A).
I've attached a sample spreadsheet with random values for reference.
1) Columns A-D - base to start getting data (using RAND)
2) Columns G-J - valued data from A-D, with some minor adjustments
3) Columns M-P - pivot table showing totals by client by year filtered by attorney
4) Columns S-V - same pivot table as M-P showing values as Rank
5) Columns W-X - VALUE formula to value the ranks in T-U
5) Column Y - formula to derive client if in top 10 in either 2014 or 2015
6) Column Z - array formula to arrange clients in column Y removing the 0 value lines
7) Columns AA-AB - index formula to N-O to pull total values
8) Chart starting in AE - plotting Z24:AB24
Currently filter in pivot tables is set to A4, and Chart is plotting all of the clients correctly; however, if I change the attorney to A2, the first value in the chart shows as #N/A this attorney does not have the same number of top 10 clients, so I only need to plot Z23:AB23 for this attorney. Is there a way to exclude the #N/A from the vertical axis automatically so when changing the attorney, the graph will add/subtract lines as necessary?
Any help would be greatly appreciated.
Thank you,
YL
I've attached a sample spreadsheet with random values for reference.
1) Columns A-D - base to start getting data (using RAND)
2) Columns G-J - valued data from A-D, with some minor adjustments
3) Columns M-P - pivot table showing totals by client by year filtered by attorney
4) Columns S-V - same pivot table as M-P showing values as Rank
5) Columns W-X - VALUE formula to value the ranks in T-U
5) Column Y - formula to derive client if in top 10 in either 2014 or 2015
6) Column Z - array formula to arrange clients in column Y removing the 0 value lines
7) Columns AA-AB - index formula to N-O to pull total values
8) Chart starting in AE - plotting Z24:AB24
Currently filter in pivot tables is set to A4, and Chart is plotting all of the clients correctly; however, if I change the attorney to A2, the first value in the chart shows as #N/A this attorney does not have the same number of top 10 clients, so I only need to plot Z23:AB23 for this attorney. Is there a way to exclude the #N/A from the vertical axis automatically so when changing the attorney, the graph will add/subtract lines as necessary?
Any help would be greatly appreciated.
Thank you,
YL