Pivot table Blanks

E

Earl Kiosterud

Dean,

"Blank" can happen when the data range for the pivot table extends beyond
the last record in your data. If you have a varying count of rows, and at
least one column has no empty cells (to the last record, not beyond), you
can use the dynamic range technique. Post back.

Another option is to right-click the "blank" pivot table field, and choose
hide.
 
D

Dkso

Earl,
thanks for your reply and sorry the the delay in mine. If I read your
message correctly it's because I use a static range for my data, this
range is the full columns A -> whatever, the data in this range is
updated by an MSQuery which will have varying amounts of data each
time.

Without having to select the data each time is there another way to
set the range?

Dean
 
D

Debra Dalgleish

When you get data with MS Query, it should be in a range that's
automatically named, e.g. Query_from_MS_Access_Database
To see the name, select the sheet that contains the data, and choose
Insert>Name>Define

Change the pivot table to use this range as the source:
Select a cell in the pivot table
Choose PivotTable>Wizard
Click the Back button
Enter the MS Query range name
Click Finish
 
D

Debra Dalgleish

You can name a range that is offset from the query range:

Choose Insert>Name>Define
Type a name for the range, e.g. PivotData
Type an offset formula, e.g.:
=OFFSET(Sheet1!Query_from_MS_Access_Database,0,0,,10)
where 10 is the number of columns required to include your calculations
Click Add, click OK

Use this range as the source for the pivot table.
 
Top