Susan Harkins
ssharkins at setel.com
Mon Jul 9 11:06:26 CDT 2007
I'll give it a try -- thanks Jim! Susan H. -----Original Message----- From: dba-tech-bounces at databaseadvisors.com [mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Hale, Jim Sent: Monday, July 09, 2007 11:05 AM To: Discussion of Hardware and Software issues Subject: Re: [dba-Tech] Excel -- dynamic range name A better way is to use a formula. Go Insert, name, define, in the "names in workbook" box type the name you want for the range. In the "refers to" box type =offset($a$1,0,0,counta($a$1:$a$10000),counta($1:$1)). The range will change dynamically as rows or columns are added. To check go edit, goto and enter the name of your range and click OK. The entire range should be highlighted. If the range is greater than 10,000 records you have to increase $a$10000. You can of course use $a$65000 to cover the entire column but this slows things down. Jim Hale -----Original Message----- From: dba-tech-bounces at databaseadvisors.com [mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Sunday, July 08, 2007 8:07 PM To: 'Discussion of Hardware and Software issues' Subject: [dba-Tech] Excel -- dynamic range name The only way I know of to create a dynamic range name when the list of items increases/decreases is to create a range name that's one cell more than needed and insert a row before adding a new record -- that stretches the range to include the new row. Is this the best way? Susan H. _______________________________________________ dba-Tech mailing list dba-Tech at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-tech Website: http://www.databaseadvisors.com *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email. _______________________________________________ dba-Tech mailing list dba-Tech at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-tech Website: http://www.databaseadvisors.com No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.10.2/890 - Release Date: 7/7/2007 3:26 PM