External Data Ranges

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

In a book about Excel I read that it is not a good
practice to put anything under external data ranges. I
have done this and have had no problems. Is this true?
And if so why? The data I am entering does not come near
the limit of rows for Excel. My use of external data
ranges is for custom reporting solutions. Using external
data ranges gives me more flexibility in creating custom
reports than pivot tables. For example I can format more
flexibly and I can summarize more flexibly. I can also
use parameters with external data ranges and I can't do
this (or I am not aware of how to do this) with PivotTables
(r).
 
In a book about Excel I read that it is not a good
practice to put anything under external data ranges. I
have done this and have had no problems. Is this true?
And if so why? The data I am entering does not come near
the limit of rows for Excel. My use of external data
ranges is for custom reporting solutions. Using external
data ranges gives me more flexibility in creating custom
reports than pivot tables. For example I can format more
flexibly and I can summarize more flexibly. I can also
use parameters with external data ranges and I can't do
this (or I am not aware of how to do this) with PivotTables
(r).
Click to expand...

The first rule about reading any How-To books is figuring out when to discount
what they say. Read most spreadsheet design guides, and you'll see it's not a
good idea to use multiple worksheets or formulas using more than 3 function
calls and a slew of other commonly used idioms and features.

There are issues of speed and fragility when using external data. For novice to
intermediate users, the reasons to avoid these often outweighs the advantages.
For more advanced users, the trade-off changes. It's possible the person who
wrote the book to which you refer had in mind users with lesser spreadsheet
skills and experience. [Besides, how many advanced users buy How-To books?]
 
Thanks for the reply. I wanted to make sure. I was doing
it before I read the how-to book. The concern came
because it was a Microsoft(r) press how-to book.

I'm going to go ahead and use them the way I was.
-----Original Message-----
In a book about Excel I read that it is not a good
practice to put anything under external data ranges. I
have done this and have had no problems. Is this true?
And if so why? The data I am entering does not come near
the limit of rows for Excel. My use of external data
ranges is for custom reporting solutions. Using external
data ranges gives me more flexibility in creating custom
reports than pivot tables. For example I can format more
flexibly and I can summarize more flexibly. I can also
use parameters with external data ranges and I can't do
this (or I am not aware of how to do this) with PivotTables
(r).
Click to expand...

The first rule about reading any How-To books is figuring out when to discount
what they say. Read most spreadsheet design guides, and you'll see it's not a
good idea to use multiple worksheets or formulas using more than 3 function
calls and a slew of other commonly used idioms and features.

There are issues of speed and fragility when using external data. For novice to
intermediate users, the reasons to avoid these often outweighs the advantages.
For more advanced users, the trade-off changes. It's possible the person who
wrote the book to which you refer had in mind users with lesser spreadsheet
skills and experience. [Besides, how many advanced users buy How-To books?]
Click to expand...
 
Curtis

I agree with Harlan. There is an option that causes external data tables to
overwrite (instead of push down) extraneous data when the table grows. My
guess is that is the cause for the warning. You can read about those
options here

http://www.dicks-blog.com/excel/2004/04/data_range_prop.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Curtis said:
Thanks for the reply. I wanted to make sure. I was doing
it before I read the how-to book. The concern came
because it was a Microsoft(r) press how-to book.

I'm going to go ahead and use them the way I was.
-----Original Message-----
In a book about Excel I read that it is not a good
practice to put anything under external data ranges. I
have done this and have had no problems. Is this true?
And if so why? The data I am entering does not come near
the limit of rows for Excel. My use of external data
ranges is for custom reporting solutions. Using external
data ranges gives me more flexibility in creating custom
reports than pivot tables. For example I can format more
flexibly and I can summarize more flexibly. I can also
use parameters with external data ranges and I can't do
this (or I am not aware of how to do this) with PivotTables
(r).
Click to expand...

The first rule about reading any How-To books is figuring out when to discount
what they say. Read most spreadsheet design guides, and you'll see it's not a
good idea to use multiple worksheets or formulas using more than 3 function
calls and a slew of other commonly used idioms and features.

There are issues of speed and fragility when using external data. For novice to
intermediate users, the reasons to avoid these often outweighs the advantages.
For more advanced users, the trade-off changes. It's possible the person who
wrote the book to which you refer had in mind users with lesser spreadsheet
skills and experience. [Besides, how many advanced users buy How-To books?]
Click to expand...
Click to expand...
 
Back
Top