Pivot table range selection changes when opening 2003 xls in Excel2007

[Saqib Ali]

Has anyone noticed that Pivot table range selection gets messed up
when opening a Excel 2003 document in Excel 2007? This causes some of
the data to be left out from the table, which causes confusion.

How can I prevent this from happening. We don't wanna redo all the
spreadsheets in Excel 2007.....

Thanks.

saqib
http://doctrina.wordpress.com/

 

[roni]

Has anyone noticed that Pivot table range selection gets messed up
when opening a Excel 2003 document in Excel 2007? This causes some of
the data to be left out from the table, which causes confusion.

How can I prevent this from happening. We don't wanna redo all the
spreadsheets in Excel 2007.....

Thanks.

saqibhttp://doctrina.wordpress.com/

try sisense. They create pivot tables that are easier to use and drill
down into. http://www.sisense.com

 

[Pete_UK]

Stop SPAMMING these groups.

Pete

try sisense. They create pivot tables that are easier to use and drill
down into.http://www.sisense.com

 

[Saqib Ali]

Hello everybody,

Any thoughts on how to resolve this issue? It happens on all excel
documents, where the entire column was selected for the range. After
conversion to Excel 2007, the selected range is truncated to 16384
rows of data. We have way more rows in the spreadsheets than 16,384.

I have created a video of the issue, which is available @
http://sites.google.com/site/libertasjustitiaveritas/other-stuff/Pivot-Table-Range-Issue


saqib
http://doctrina.wordpress.com/

 

[Ed Ferrero]

Hi Saqib,

I can reproduce the problem. It seems to be a bug in Excel 2007.

Usually, I use dynamic ranges to define the data range for a Pivot Table
rather than selecting the whole column.

i.e. create a named range say 'data' with a formula like
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),7)

This returns a range starting at A1 with 7 columns and whatever number of
rows that have data in column A.

It seems to get around the problem.

You can find more information on dynamic ranges in the Excel database
tutorial at http://www.edferrero.com/ExcelTutorials/tabid/54/Default.aspx

Ed Ferrero
www.edferrero.com