Pivot Tables in Excel 2007
 

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

Pivot Tables in Excel 2007
 

beata wrote:
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
--
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.

Pivot Tables in Excel 2007
 

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?

"houghi" wrote:

beata wrote:
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
--
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.

Pivot Tables in Excel 2007
 

Hi

I don't know whether this will help or not, but try
Round Office buttonExcel optionsAdvancedEditing OptionsUncheck 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 InsertTable 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
InsertnameDefine 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
--
Regards
Roger Govier

"beata" wrote in message
...
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?

"houghi" wrote:

beata wrote:
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
--
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.

Pivot Tables in Excel 2007
 

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" wrote:

Hi

I don't know whether this will help or not, but try
Round Office buttonExcel optionsAdvancedEditing OptionsUncheck 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 InsertTable 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
InsertnameDefine 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
--
Regards
Roger Govier

"beata" wrote in message
...
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?

"houghi" wrote:

beata wrote:
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
--
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.

Pivot Tables in Excel 2007
 

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
--
Regards
Roger Govier

"beata" wrote in message
...
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" wrote:

Hi

I don't know whether this will help or not, but try
Round Office buttonExcel optionsAdvancedEditing OptionsUncheck 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 InsertTable 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
InsertnameDefine 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
--
Regards
Roger Govier

"beata" wrote in message
...
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?

"houghi" wrote:

beata wrote:
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
--
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.

Pivot Tables in Excel 2007
 

sadly.. I didn't get the obvious!



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

"Roger Govier" wrote:

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
--
Regards
Roger Govier

"beata" wrote in message
...
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" wrote:

Hi

I don't know whether this will help or not, but try
Round Office buttonExcel optionsAdvancedEditing OptionsUncheck 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 InsertTable 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
InsertnameDefine 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
--
Regards
Roger Govier

"beata" wrote in message
...
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?

"houghi" wrote:

beata wrote:
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
--
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.

Pivot Tables in Excel 2007
 

roger at technology4u dot co dot uk

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

--
Regards
Roger Govier

"beata" wrote in message
...
sadly.. I didn't get the obvious!



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

"Roger Govier" wrote:

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
--
Regards
Roger Govier

"beata" wrote in message
...
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" wrote:

Hi

I don't know whether this will help or not, but try
Round Office buttonExcel optionsAdvancedEditing OptionsUncheck
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 InsertTable 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
InsertnameDefine 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
--
Regards
Roger Govier

"beata" wrote in message
...
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?

"houghi" wrote:

beata wrote:
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
--
At the source of every error which is blamed on the computer you
will
find at least two human errors, including the error of blaming it
on
the computer.

Pivot Tables in Excel 2007
 

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.

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
roger at technology4u dot co dot uk

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

--
Regards
Roger Govier

"beata" wrote in message
...
sadly.. I didn't get the obvious!



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

"Roger Govier" wrote:

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
--
Regards
Roger Govier

"beata" wrote in message
...
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" wrote:

Hi

I don't know whether this will help or not, but try
Round Office buttonExcel optionsAdvancedEditing OptionsUncheck
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 InsertTable 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
InsertnameDefine 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
--
Regards
Roger Govier

"beata" wrote in message
...
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?

"houghi" wrote:

beata wrote:
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
--
At the source of every error which is blamed on the computer you
will
find at least two human errors, including the error of blaming it
on
the computer.