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

[LINSEY]

I posted this question yesterday, so i'm sorry for reposting but I really need help.

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

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

STYLE DESCRIPTION
LYW029 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

I don't want to use VLOOKUP because I may need to rearrange the columns in the database. I have a whole form full of data to pull and I would have to rewrite the formulas everytime I added/deleted/moved a column in the database.

I have no experience with code.

Please help!

Linsey

 

[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.

You'll likely notice that the above is what I had posted to your other posting yesterday.

If you wish, we can wait for others to post other possible solutions. However, if none are received... as I had mentioned, I'm prepared to help with the VBA code. I believe the above option is the only practical one. It's not a difficult task, and it could be set up so the end-user will not know that the merge of data has taken place.

As for the VBA code, it would be preferable to have some kind of file (information) to work with - it'll make it easier on both of us.

A "generic" VBA example could be posted, but it'll be more difficult and time-consuming for you to modify it to fit the specifics of your application.

I hope this at least provides you with options.

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

 

[LINSEY]

Dale,

I'm sending you an email with my database and sample worksheet attached.

Thanks so much for trying to help me!

Linsey

 

[SkipVought]

Linsey

From you question, I don't believe that you have things correctly defined.

1. In order to use STYLE in your DGET function, STYLE would have to be a named range, with the value of your STYLE workbook name.

2. CRIT is ALSO a named range of AT LEAST 2 CELLS. The cell(s) in the first row of CRIT are Heading Value(s) in your database that you want as criteria elements and then the row(s) following are data values, used to qualify the resultset to A SINGLE ROW OF DATA from the DESCRIPTION column.

I posted a reply to you yesteday, on thread68-658275 as did Dale. You stated that it still was not working. It seem that you've not things straight.

Here's a suggestion. Make a very simple table on sheet1: Name Age Country
Skip 63 USA
Dale 35 Canada
Linsy 29 Unknown

Then on Sheet2, set up a DGET in A1 and the following Criteria in C1:C2

Country
Unknown

=DGET(Sheet1!A1:B4,"Age",C1:C2)

will return 29

See if you can make this work. If you can, it is a SMALL STEP to solving your problem

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[LINSEY]

Skip,

I have defined my database as STYLE in the workbook where I am writing the formula.

I have also defined two cells as my criteria named CRIT.

Here's a little more detail:

DATABASE: "STYLE"
STYLE[tab][tab]VERSION[tab][tab]DESCRIPTION
LYW029[tab][tab]001[tab][tab][tab]12 oz. RHT
LYW029[tab][tab]002[tab][tab][tab]13 oz. RHT

DIFFERENT WORKBOOK:
Cell A1 = LYW029
Cell A2 = 001

Range Named "CRIT":
Cell H1 = Blank
Cell H2 = A1&A2

Formula:
DGET(STYLE,"DESCRIPTION",CRIT)

Thanks to Dale's help, this formula works fine when it is the same workbook (different worksheet) as the database.

I just don't understand why it won't work in this situation when I have defined all applicable ranges.

Linsey

 

[Zathras]

Not sure what your problem is. As long as both workbooks are open, it should work ok. Using your example, I called the database sheet STYLES and the range name STYLE in the current workbook is defined as:
[blue]

  =[STYLES.xls]Sheet1!$B$2:$D$4

[/color]
It doesn't affect the execution of the DGET, but your CRIT range might be a little easier to understand if you set it up this way (include all 4 cells in the range definition):
[blue]

   STYLE  VERSION
    =A1     =A2

[/color]

 

[SkipVought]

Lindsy,

There is OFTEN a problem with numbers used as characters. Understand that string "001" is a much different value than 001.

I could not make the DGET function work with your data.

I could make it work with several changes, the FIRST is CRITICAL...
If STYLE is a named range that has the name of your workbook in it then youf formula needs INDIRECT to work.

=DGET(INDIRECT(STYLE),"DESCRIPTION",CRIT)

The second involves the CRITERIA. The value of

=A1&A2

in H2 with H1 empty did not work. Maybe Dale has an answer for that.

What DID work was CRIT in H1:I2

STYLE       VERSION        	
=A1         =A2
/code]
Now this did work AFTER I conditioned the VERSION column to be numeric, ie 1, 2 etc OR '001, '002 in both the VERSION column and in the CRIT DIRECTLY instead of =A2, 'cuz =A2 gets you 1, 2, etc.

If you use numbers as characters, you will almost always have problems.

Hope this adds some light  :-)


Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[LINSEY]

I think I figured it out by myself...

Cell A1 Defined as "STYLEINPUT"
Cell A2 Defined as "VERSIONINPUT"

Formula in H2 should be:
AND(STYLE=STYLEINPUT,VERSION=VERSIONINPUT)

It's working for me now.

Thanks for talking me through this guys!!!

Linsey

 

[Zathras]

Skip, you're right I should have pointed out the difference between [blue]001[/color] and [blue]'001[/color].

I get the same results whether or not INDIRECT is used. In both cases both worksheets need to be open (Excel 97). Maybe 2K can work with a closed worksheet? However, when using INDIRECT, the reference to STYLE has to be in double-quotes: I can use either
[blue]

  =DGET(INDIRECT("STYLE"),"DESCRIPTION",CRIT)

[/color]
or
[blue]

  =DGET(STYLE,"DESCRIPTION",CRIT)

[/color]
to get the result.

Also, I don't understand =A1&A2 as a CRIT either. I didn't try it since it came (apparently) from Dale, so naturally I assumed it was ok. But I set up my test bench using the "traditional" way to express a database criteria expression because it is easier to read.