Excel 2000: Using DGET function when database is in another workbook

[LINSEY]

I want to use the DGET function to extract information from a database that is in a different workbook.

I tried defining the database in my new workbook but I'm not sure how to write the formula correctly.

DATABASE WORKBOOK (defined in new workbook as "STYLE&quot:

STYLE[tab][tab]DESCRIPTION
LYW029[tab][tab]12 oz. RHT

FORMULA IN NEW WORKBOOK:

DGET(STYLE,"DESCRIPTION",CRIT)

CRIT=cell in new workbook that contains the style to look up in database workbook

What am I doing wrong?

Linsey

 

[SkipVought]

Hi,

The easiest way to "write" formulas is to, whilst editing/writing the formula, select the objects as they are encountered in the formula.

For instance, if I wanted to add A1 from Workbook1, Sheet1 to A2 from Workbook2, Sheet2, then i would

1. edit my cell
2. type =
3. from menu item Window, select Workbook1
4. while in Workbook1 select the sheet tab for Sheet1
5. while in Sheet1, select A1
6. type +
7. perform steps 3-5 for Workbook2, Sheet2, A2
8. hit [ENTER]

and...

VOLA!

I have a formula that works,

and I did not have to remember the workbook/worksheet/cell syntax!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[LINSEY]

Skip,

I tried that, but the DGET function requires you to name the database and you are supposed to use the field name in quotes to designate the column you are retrieving.

linsey

 

[SkipVought]

You can still do that...

=DGET([vbaMatlWIP.xls]Query!$H$1:$L$18,"MyList",J1:M3)

for instance. ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[LINSEY]

Skip,

That formula is still not working for me.

Linsey

 

[SkipVought]

and the workbook with the database IS open, right?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[LINSEY]

it is open, but i would like for the function to work even if it is not.

 

[DaleWatson123321]

LINSEY,

Here's an option. It involves having VBA code that responds to the user's input for the DGET criteria, by (behind the scene):

a) opening the other file,

b) copying the data to a pre-set worksheet that the DGET function references,

c) closing the file.

I hope this helps. If you need help with the code, let us know. Either Skip or I can oblige.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

1. That workbook is going to have to be open!

2. Are you sure that your criteria selects ONLY ONE VALUE.

If the criterial that you are using does NOT select a single row in your database, the function will return a #NUM error.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com