Pivot Tables in Excel 2007

[beata]

Hi. We installed Excel 2007 a couple of days ago.

All of our existing - and newly created - pivot tables have a data range
that goes to
data!$A$1:$H$16384

Most of our worksheets go beyond that (and always have).

Is there a way to change this seeming "default"

Thanks,
beata

 

[houghi]

Hi. We installed Excel 2007 a couple of days ago.

All of our existing - and newly created - pivot tables have a data range
that goes to
data!$A$1:$H$16384

Most of our worksheets go beyond that (and always have).

Is there a way to change this seeming "default"

Select data!$A$H as source. I use this for data that has variable
length.

houghi

 

[beata]

Thanks. I actually think I did.. I never "select" range.. but instead define
it by columns, exactly as you suggest (I got burned too many times by
extending elow ranges)

So I don't THINK that's it

any other ideas?

 

[Roger Govier]

Hi

I don't know whether this will help or not, but try
Round Office button>Excel options>Advanced>Editing Options>Uncheck Alert
user when cells used exceeds .....

Personally, I would never use whole columns in a PT source range, especially
in XL2007 where there are over 1 million rows.
In XL2007, use Insert>Table which will give a dynamically increasing range
(complete with extension of formulae) and use this as the source range for
the PT.

If you don't want to use the Table method, then define a Dynamic range for
your Data
Insert>name>Define> name myData Refers to
=$A$1:INDEX($1:$1000000,COUNTA($A:$A),COUNTA($1:$1))
This will grow both in length and width as data is added.

If you know you are only going as far as column M for example, but the rows
would be expanding, then this could be simplified to
=$A$1:INDEX($M:$M,COUNTA($A:$A))

Set myData as the source for your PT

 

[beata]

Hi. I figured it out (??). I was saving it as an Excel 2003 file so my
customer base could read it. Apparently, when I did that.. I listerally LOST
all the data after row 16,384.

I did a couple of tests and apparently when I save it "Correctly" it's fine.

(of course, my customers have to download the compatibility pack).

I'm in the process of determining whether I should just "go back."

To ME -- this is a serious bug. It would be one thing if I couldn't save
more than 65,000 rows in Excel 2003 -- but to not be able to save more than
16,384 -- that's not cool.

beata

 

[Roger Govier]

No, that is not the reason. XL2007 does not impose a limitation of 16384
rows when saving in Compatibility mode.

I have just saved a file with 34300 rows in Compatibility mode.
Loaded it in XL2003 and all data is there.

If you want to send me a sample of one of your 2007 files, I will take a
look and see if I can spot anything.
To send direct, mail to
roger at technology4u dot co dot uk
Do the obvious with at and dot

 

[beata]

sadly.. I didn't get the obvious!

(e-mail address removed)4u.dot.co.dot.uk

didn't work.. where did I mess up?

 

[Roger Govier]

roger at technology4u dot co dot uk

remove all spaces and replace "at" with "@" and "dot" with "." (omitting
the quotes)

 

[Roger Govier]

Hi Beata
File received and returned.
Saving in Excel5.0/95 format, XL2007 behaves quite correctly and limits rows
to 16384 - the limit for that version of Excel.

Saving in XL97-2003 format, I quite happily saved a file with 65533 rows,
and opened again in XL2003 with all rows present.
I returned a copy of the file to you direct.