database question

buz

Board Regular
Joined
May 30, 2002
Messages
230
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Yes, one per sheet using locally defined names...

Sheet1!Database
Sheet2!Database
Sheet3!Database

...instead of globally defined name...

Database.
 

buz

Board Regular
Joined
May 30, 2002
Messages
230
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

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

Board Regular
Joined
May 30, 2002
Messages
230

ADVERTISEMENT

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

Board Regular
Joined
May 30, 2002
Messages
230
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

Board Regular
Joined
May 30, 2002
Messages
230
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top