- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
I'm working with a team member on setting up a file to keep track of a maitnenace checklist. She's got tasks that will be repeated daily, weekly, monthly, quarterly, etc.. Her initial attempt was to set up a page for each type of task and was manually entering them all on a summary sheet. Yikes!
She asked for advice, so I'm working with her on setting up a master data page, and we're creating pivot tables to show the weekly, monthly, etc... pages.
Here's the thing though.... there are no numbers to throw in the "Data" section of the report. We're basically resummarizing most of the info in the row label fields, and building a list that way. I have the file set to display in tabular format with repeating labels (Excel 2010.)
The kicker is that if there are blank cells in my data, the table fills them in with "(blank)". Changing the pivot table options to display nothing for blank cells doesn't seem to have any effect... I'm guessing this is for the values section, not the labels section.
I can skirt it by putting a single quote in every blank cell in my data area (it's not blank then), but that's a maintenance nightmare and really shouldn't be necessary. There is no way I can rely on users to keep that up.
I can also apply conditional formatting to the pivot table to colour the cells white if they contain the value "(blank)". It works, but when the pivot table expands, the conditional format range doesn't get adjusted. I know I can get around it by covering a huge amount of cells with the conditional format, but that seems like a lot of overhead.
Does anyone have any better suggestions on how to hide blank values in the row labels field, or an I stuck with workarounds? It seems to me that there should be a built-in way to make this happen.
She asked for advice, so I'm working with her on setting up a master data page, and we're creating pivot tables to show the weekly, monthly, etc... pages.
Here's the thing though.... there are no numbers to throw in the "Data" section of the report. We're basically resummarizing most of the info in the row label fields, and building a list that way. I have the file set to display in tabular format with repeating labels (Excel 2010.)
The kicker is that if there are blank cells in my data, the table fills them in with "(blank)". Changing the pivot table options to display nothing for blank cells doesn't seem to have any effect... I'm guessing this is for the values section, not the labels section.
I can skirt it by putting a single quote in every blank cell in my data area (it's not blank then), but that's a maintenance nightmare and really shouldn't be necessary. There is no way I can rely on users to keep that up.
I can also apply conditional formatting to the pivot table to colour the cells white if they contain the value "(blank)". It works, but when the pivot table expands, the conditional format range doesn't get adjusted. I know I can get around it by covering a huge amount of cells with the conditional format, but that seems like a lot of overhead.
Does anyone have any better suggestions on how to hide blank values in the row labels field, or an I stuck with workarounds? It seems to me that there should be a built-in way to make this happen.