Removing the BLANK option in a Filter

[Aaron Young]

Hi folks

This troublesome spreadsheet of mine has once again done something odd.

The data range recognised by the Pivot Table was exceeded, so I updated the Data Source to go beyond the last cell: originally it was $B$1:$J$15002 which I have now changed to $B$1:$J$17000 to allow for the data I will add to it over the next couple of weeks.

Now, the Pivot Table filter that relies on column B have thrown in the Blanks option, though there are no blank fields in that tab (except for formatting). Prior to changing the Data Source parameters and before the data reached row 15002 the Blanks options did not appear, yet the formatting was true for those row then, too.

Ironically, by changing the Data Source parameters, the problem I had with an old filter option appearing where it longer existed in the data was rectified!

This blank filter option will cause me problems when I refresh the Pivot table and the Blanks "column" wants to overwrite my data directly beside the PT.

Any assistance would be appreciated.

Aaron

 

[Smallman]

Hi Aaron

I think if you make your source data a table the Pivot table will automatically recognise the last row of the table.

If you don't want to do that then make your pivot table run off a dynamic range then the blanks option will not exist.

Call your named range Source then add this.

=OFFSET('Reopen Task'!$B$1,0,0,COUNTA('Reopen Task'!$B$1:$B$20000),8)

Now change the Pivot table source to =Source

Should get you over the line.

Take care

Smallman

 

[Aaron Young]

Thanx Smallman

I know this is going to sound like a dumb question, but:

Call your named range Source then add this.

=OFFSET('Reopen Task'!$B$1,0,0,COUNTA('Reopen Task'!$B$1:$B$20000),8)

Now change the Pivot table source to =Source

Where do I "add this"? I have named the range, but I don't know where to put the new formula...

 

[Smallman]

Hi Aaron

It is a named range so you put it here;

Smallman

 

[Aaron Young]

That's terrific, thanx Smallman!

Worked perfectly. That's the first time I have used that feature.

Cheers!

 

[Smallman]

No worries mate

Take it easy

Smallman