Set Database in Excel
 

In Microsoft article 183446 under method 2, it refers to using "Set Database"
on the Data menu. I am using Excel 2002 (the article says it applies to
Excel 2002), but Set Database does not appear in my Data menu. Is this an
error?

It's an error due, I think, to very old menu structures. The article says
that the information applies to everything from version 5.0 (1995) through
2003, inclusive. But I believe that the Set Database menu item disappeared
from the Data menu as of Excel 97. And one has long defined a name using the
Insert menu, not the Formula menu. But my rapidly deteriorating neurons
whisper that once upon a time there was a Define Name item in the Formula
menu.

Using any version since and including Excel 97, and assuming per 183446 that
you want a chart to update automatically when you add new data, you could
(using the article's example data layout) define the name Date as
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1) and Sales as =OFFSET(Date,0,1). In that
case, you would not bother to define a Database name. Be careful that you
have no extraneous values in column A -- else the COUNTA function will
return too large a result.

BTW, there still is a Set Database statement in VBA if you first add a
reference to DAO; using ADO you'd likely use Set [declared ADO connection].

C^2

Conrad Carlberg
Excel Sales Forecasting for Dummies, Wiley, 2005

--

"joeeng" wrote in message
...
In Microsoft article 183446 under method 2, it refers to using "Set
Database"
on the Data menu. I am using Excel 2002 (the article says it applies to
Excel 2002), but Set Database does not appear in my Data menu. Is this an
error?

Thanks for the historical info.

"Conrad Carlberg" wrote:

It's an error due, I think, to very old menu structures. The article says
that the information applies to everything from version 5.0 (1995) through
2003, inclusive. But I believe that the Set Database menu item disappeared
from the Data menu as of Excel 97. And one has long defined a name using the
Insert menu, not the Formula menu. But my rapidly deteriorating neurons
whisper that once upon a time there was a Define Name item in the Formula
menu.

Using any version since and including Excel 97, and assuming per 183446 that
you want a chart to update automatically when you add new data, you could
(using the article's example data layout) define the name Date as
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1) and Sales as =OFFSET(Date,0,1). In that
case, you would not bother to define a Database name. Be careful that you
have no extraneous values in column A -- else the COUNTA function will
return too large a result.

BTW, there still is a Set Database statement in VBA if you first add a
reference to DAO; using ADO you'd likely use Set [declared ADO connection].

C^2

Conrad Carlberg
Excel Sales Forecasting for Dummies, Wiley, 2005

--

"joeeng" wrote in message
...
In Microsoft article 183446 under method 2, it refers to using "Set
Database"
on the Data menu. I am using Excel 2002 (the article says it applies to
Excel 2002), but Set Database does not appear in my Data menu. Is this an
error?

At one time you had to define your range of records by manually specifying
the cells it occupied (eg., B1:K535) in order to use the data management
features of the program. Excel has long since been educated to recognize the
data range automatically as long as you have your active cell within the
range of consecutive rows/columns occupied by the data. Apparently that
article was out of date before it was published.

Regards |:)





On 8/22/05 3:58 PM, in article
, "joeeng"
wrote:

In Microsoft article 183446 under method 2, it refers to using "Set Database"
on the Data menu. I am using Excel 2002 (the article says it applies to
Excel 2002), but Set Database does not appear in my Data menu. Is this an
error?