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]

I think, but am not sure, that you can have one per sheet. Really don't know. I ALWAYS use the Dynamic Named Range approach, however, because it allows me to use a descriptive name for my table(s).

 

[Mark W.]

Yes, one per sheet using locally defined names...

Sheet1!Database
Sheet2!Database
Sheet3!Database

...instead of globally defined name...

Database.

 

[buz]

I'll play with this, but am wondering then - if Data>Form requires the range to be named "database", then will "SHEET1!database" enable Data>Form?

Would be great if it did.

thanks for the input.

 

[lenze]

Buz:
The Data>Form Feature does not care what you name the range. It will still function for data entry, editing, etc. By naming it database, you enable the feature that allows the range to expand as records are added. You can have one range named database on each sheet. Each will work with the Data>Form feature. The local name Mark refered to Sheet1!database is how you refer to a particular one in a formula, Pivot Table, or other reference.

But, why not eliminate any confusion and use dynamic named ranges that expand each with unique name? You can still use Data>Form for data entry.
This message was edited by lenze on 2002-09-06 10:36

 

[Mark W.]

Actually, Sheet1!Database is determined by how it is named using the Insert | Name | Define... menu command. If you have a globally defined name for Database and a locally defined one (i.e., Sheet1!Database) that refer to different ranges then a reference to the name "Database" from any worksheet other than Sheet1 will default to the global definition.

The nice thing about using "Database" instead of your own names (e.g., my_data, range1, etc.) is that the PivotTable wizard and the Advanced AutoFilter utility recognizes the presence of the defined name, Database, and uses it by default.
This message was edited by Mark W. on 2002-09-06 10:54

 

[buz]

Lenze

On 2002-09-06 10:34, lenze wrote:
Buz:
The Data>Form Feature does not care what you name the range. It will still function for data entry, editing, etc.

Are you sure? I have used Data>Form with a range named other than database and it will indeed add to the DB (not to the range) but it will not edit (I think). Using it for an entry then using 'criteria' to find it - it will not find the entry and I think therefore be uneditable.

By naming it database, you enable the feature that allows the range to expand as records are added. You can have one range named database on each sheet. Each will work with the Data>Form feature.

Aren't the range names unique to the workbook. How do you assign a range name to a particular sheet?

The local name Mark refered to Sheet1!database is how you refer to a particular one in a formula, Pivot Table, or other reference.

But, why not eliminate any confusion and use dynamic named ranges that expand each with unique name? You can still use Data>Form for data entry.

I see - I think. In this, are you referring to using the formula method of naming data ranges.

Two things here - 1) I haven't used the 'quote' feature of this forum so I hope I am doing it right.... and 2) I am no more than an advanced novice with XL so please excuse any confusion I may have.
This message was edited by buz on 2002-09-06 21:24
This message was edited by buz on 2002-09-06 21:44

 

[buz]

Mark

On 2002-09-06 10:49, Mark W. wrote:
If you have a globally defined name for Database and a locally defined one (i.e., Sheet1!Database) that refer to different ranges then a reference to the name "Database" from any worksheet other than Sheet1 will default to the global definition.
This message was edited by Mark W. on 2002-09-06 10:54

I think I understand what you are saying here, I'm going to have to work with this a bit to see. Your reply here makes me wonder if you can have a locally defined DB (SHEET1!database) and have a PT dynamically refer to it.

See my 2 qualifiers to lenze.

 

[Mark W.]

On 2002-09-06 21:36, buz wrote:
Mark

I think I understand what you are saying here, I'm going to have to work with this a bit to see. Your reply here makes me wonder if you can have a locally defined DB (SHEET1!database) and have a PT dynamically refer to it.

See my 2 qualifiers to lenze.

I don't know what you mean by "...have a PT dynamically refer to it". I suggested that you define a name, 'Database', that refers to a cell range consisting of absolute references for use with a data form. Typically, a dynamic named range refers to a formula -- not a just a cell range.
This message was edited by Mark W. on 2002-09-07 07:55

 

[buz]

On 2002-09-07 07:46, Mark W. wrote:


I don't know what you mean by "...have a PT dynamically refer to it". I suggested that you define a name, 'Database', that refers to a cell range consisting of absolute references for use with a data form. Typically, a dynamic named range refers to a formula -- not a just a cell range.
This message was edited by Mark W. on 2002-09-07 07:55

Mark - I'm just not familiar enough with the terminology. Defining names for a cell range is the way I have been doing it. My interest was in having different databases on diff sheets. All I meant was if you could have a second database named SHEET!database could you produce a PT from it. The answer I found is yes.

So rather than having one database with many columns I can separate my data into multiple DB's, each with just a few columns.

Just a novice stumbling around.

tfyh