Big Country Posted January 10, 2012 Share Posted January 10, 2012 Got a new one here - I'll be getting a file with source data containing employee ratings for the year. For out purposes, let's say there are two categories for the ratings, each containing 4 options - IE For Category 1 you get a rating a A, B, C or D and for category 2 you get a rating of A, B, C or D and you're combined rating is something like A-A, A-B etc. I've set the pivot table up such that category 1 are my columns and category 2 are my rows, with a count in the data area to show the distribution of ratings. I have selected show items with no data. The problem I am encountering is that while it works fine with dummy data that has some distribution across all ratings, the actuality is that it is very possible to end up with no one having say D rating in either category, so "D' does not ever show up in my Pivot Table source data. Is there anyway for me to hardcode the options in the pivot table so that I get all 4 in both category 1 and 2? I need to do this because I have the pivot table results linked to another table, and am using slicers to update the PTable which in turn updates this table that we will be showing to management. Like I said, everything worked great when I had all ratings in dummy data, but I didn't think about the possibility of a rating not being used, and the table we show needs to show all options. I google searched and found that there is a built in option to do this for dates, but nothing for text values. Any thoughts? I'd consider going the route of several lines of dummy data and simply hiding those rows at the top (and may do this), but am reluctant to do so as I have several macros that cycle through this data, filter it for various options, paste the results to a new worksheet, etc., and the dummy data and hidden rows will throw a lot of this off and I need to dummy proof it as much as possible. Quote Link to comment Share on other sites More sharing options...
lennykravitz2004 Posted January 10, 2012 Share Posted January 10, 2012 Minimally tested, but came across this tab with several settings which sound like they may apply... Right click on a value under either/both column & row heading (not actual column/row heading) in your PT Choose Field Settings Choose Layout & Print tab several check boxes here could apply: Repeat item labels Show items with no data when I chose "Show items with no data", Excel added in my company (in my sheet I'm using to test) which didn't have any values to show, then I could select that company from my Row Labels drop down... Quote Link to comment Share on other sites More sharing options...
Big Country Posted January 10, 2012 Author Share Posted January 10, 2012 Minimally tested, but came across this tab with several settings which sound like they may apply... Right click on a value under either/both column & row heading (not actual column/row heading) in your PT Choose Field Settings Choose Layout & Print tab several check boxes here could apply: Repeat item labels Show items with no data when I chose "Show items with no data", Excel added in my company (in my sheet I'm using to test) which didn't have any values to show, then I could select that company from my Row Labels drop down... Already have "Show Items with No Data" checked so that all used combinations show up no matter what filters I apply.... problem is that I also need some unused combinations to show up. For now, the dummy data is working fine. Working assumption is that it should be a non-issue once we get final data tomorrow night, but if I build this based on assumptions it won't work on Thursday moring when we are live with executives/senior management Quote Link to comment Share on other sites More sharing options...
lennykravitz2004 Posted January 10, 2012 Share Posted January 10, 2012 Have you tried incorporating data validation for your data source cells using the "List" option? I'm getting ready to jump on a call, otherwise I'd try it out right now... if not, I'll give it a whirl in about an hour. Quote Link to comment Share on other sites More sharing options...
westvirginia Posted January 10, 2012 Share Posted January 10, 2012 If I understand the way your data is set up, the problem is that your rating levels (i.e. A-B, C-D, D-D) are data rather than column headings. Have I got it so far? What I might do is modify my source data to have an if/then statement for Categories one and two, so that I had a column for A-A, A-B, A-C and so forth. Then do the pivot off the formulated data so that one that has no values (i.e. D-D) would still show up in the distribution. Hope that helps! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.