database question

[buz]

I have a better understanding of 'dynamic range' DB's now. As I thought, in order to use Data>Form you MUST name your range 'database'.

My understanding is the alternative to using Data>Form is to name your DB range with a formula (=OFFSET.......).

A couple questions please. What is the difference between the two means of maintaining a DB?

It seems you can only have one DB named 'database' allowing the use of Data>Form per wkbk. Is there a way around this?

tfyh

Buz

 

[lenze]

Buz:
Now that we have really confused you, here is an explanation of how to create a dynamic named range.

Dynamic Range Formula
=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$1:$A$65536)-1,COUNTA(Sheet1!$1:$1)-1)
Note: $A$1= Anchor Cell Address (Change as needed)
Sheet1!$1:$1 Anchor Row Number (Change as needed)
The above formula gives a dynamic range that expands both by rows and columns, using the entire column or row. To limit the number of rows, change 65536 to a lower number (ex. 1000 will limit the range to 999 rows) To limit the number of columns, change the Sheet1!$1:$1 to Sheet1!$A$1:$Col :$1, where Col is the column letter of the last column you want in the range. To use, Choose Insert>Name>Define. Type a name for your range in the name box and then enter the above formula (with any adjustments) in the refers to field.
This message was edited by lenze on 2002-09-09 06:43

 

[Mark W.]

However, there's no need to employ a dynamic range if you intend to use a data form.

 

[lenze]

On 2002-09-09 07:40, Mark W. wrote:
However, there's no need to employ a dynamic range if you intend to use a data form.

Hi Mark: You are absolutely correct. I perfer to use the dynamic named range, however, to protect against someone pasting or manually entering a record in the database. I've seen too many times where someone has just copied a row down and made the changes in one or two columns. More usefull, I find, is being able to assign a meaningful name to each table. This is helpfull when six months later, I am looking at some DFUNCTION and trying to figure out what SHEET1!Database is.

 

[buz]

Who ME confused!

I get the gist of the two methods (I think).

Using the Data>Form method, it is easy for someone to come along and 'break' the dynamics of the range, rendering associated PT's useless till the 'break' is fixed. Breaking it is done by manual entry (e.g typing directly, copy and pasting) directly to the DB.

Using the formula method, I take it, is not so easy to 'break'.

So I'm still confused .... I have another question ....

Aren't both 'dynamic ranges'? One is created using a formula in the 'refers to' box and the other is created using XL's short cut which requires the name 'database' be applied to the range.

Am I close to understanding? I'm going to play with the formula method to see if I can enable it.