Exclude (blank) from Pivot table without filtering it out

[Dosaparks]

Hi All,

I have looked for answers to this one but haven't been successful finding an answer that applied to my situation.

I have a 'data' sheet where each month I paste data to. Various pivot tables and charts are then automatically updated to account for the new data.

To ensure that each pivot table picks up all data, I have made my data source 5,000 rows (one month may be 2,000 the next may be 4,000).

The issue is that due to not all 5,000 rows having data in them, all my pivot tables are reporting a (blank) value.

I understand I can filter the pivot tables so that (blank) is unselected however this results in the table no longer being 'select all'. As the next month's data comes through, as the pivot table is not select all, it won't automatically pick it up (for example, the next dump of data has an 'Oct-13' category however this doesn't appear on the pivot tables as they are only set to pick up Jul, Aug, Sep with (blanks) unselected (and now also with 'Oct-13' unselected'.

It actually isn't the pivot tables I'm worried about but my pivot charts linked to them. They have a (blank) bar which looks a bit silly.

Would be great if there was just a setting on the pivot chart to ignore blanks but I'm thinking I may need a macro which updates all pivot tables to 'select all BUT (blank)'.

Any ideas?

 

[RoryA]

If you have Excel 2007 or later, set the data source up as a Table; if not, use a dynamic range. See Debra's page here

 

[Dosaparks]

Thanks for your reply.

The 'table' option does look like a good idea.

Unfortunately it is not automatically expanding though when new data comes through. I have an advanced filter macro that drives the new data onto the 'data' worksheet.

So basically

> the data gets dumped into a 'raw' worksheet,
> the user selects their criteria (only show me these products and these regions etc.
> They click 'Update Charts' which triggers an advanced filter to the 'Data' worksheet (filters the 'raw' data into a separate worksheet)
> The macro also refreshes the pivot tables which updates the charts.

So when they click 'update charts', the new data is coming across into the 'Data' worksheet, but the 'Table' I have setup is not including it. So the pivot tables fail to pick up the new data.

If I manually 'convert to range' and then re-apply the table, it picks it up however when I record a macro it doesn't actually provide any code for this action (I thought I could just add that step to my overall macro and it would be fine)

So I think I am close, just need to work out how to get the table to update to include the new data.

Thanks for your time looking into this.

 

[RoryA]

If you're adding the data to the sheet using a macro, you don't actually need a table or dynamic range, you can simply name the range after you add the data:

Sheets("data sheet name").Range("A1").Currentregion.Name = "PivotData"

for example.

 

[Dosaparks]

That works perfectly!

I don't know a lot about macros but I am so inspired to learn when I see the wand waving wizardry that appears on these forums.

Thanks for waving your wand in my direction

Let me know if I can 'credit' you points or something for solving my problem. Not sure if I'm supposed to mark this one as solved.

 

[RoryA]

We don't do points or any of that stuff, nor do we close questions (there may always be a better/different answer to be posted later) so a simple thank you is all that's required.

 

[jpford]

I actually may have stumbled on another macro-free solution for this. In the Pivot Table, click the drop-down carrot for the field you want to remove blanks from, click "label filters," then "does not equal," and type in the box "(blank)." This will filter out any (blank) records, but does not seem to restrict new entries or require clicking the "(blank)" box the same way directly filtering it does.