Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL Server UPDATE Database from Excel Table | Links and Linking in Excel | |||
Excel Database | Excel Discussion (Misc queries) | |||
Excel as a database | Excel Discussion (Misc queries) | |||
Keeping date format of a field inserted from an excel database | Excel Worksheet Functions | |||
Query a Access database that has a module from Excel | Excel Discussion (Misc queries) |