DGET limitations...

msvec

Board Regular
Joined
Feb 18, 2002
Messages
202
or maybe not, but in using a DGET(database,field,criteria), I don't want to refer to cells for the criteria (Excel seems to force a cell reference). I want to use an array in the formula (or get around cell references somehow).

Please help. Thanks.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Can't you name your criteria range and use that name in the DGET formula. it works for me.
 
Upvote 0
On 2002-04-17 14:15, msvec wrote:
or maybe not, but in using a DGET(database,field,criteria), I don't want to refer to cells for the criteria (Excel seems to force a cell reference). I want to use an array in the formula (or get around cell references somehow).

Please help. Thanks.

I don't think D-functions accept a constant array as criteria. Why do you want to get around cell references somehow? What is the DGET formula that you're trying to devise?
 
Upvote 0
First, Aladin the Great, is a DGET more efficient than a LOOKUP function? I'm currently using a VLOOKUP and it's just fine, but I thought a DGET may be quicker...but I could be totally wrong, so if you could clear that up, I'd appreciate it.

As far as my DGET, let's say I have 3 columns in my database with the following 3 rows:
{Name, Q1, Q2}
{Item1, 11, 22}
{Item2, 33, 44}

My formula: DGET(range,"Q1", ?? )...it works if the '??' is either a named range or a cell reference. The problem is that I don't want to have to have a 2 row criteria for each DGET, of which there are many. I'd like to use an array in the formula like {"Name";"Item2"}.

thanks again for your help,
mike
 
Upvote 0
You can't replace a Dfunction's Criteria range with an array constant. Excel uses this range (in the background) as it passes thru the database to produce a result.

While you're making VLOOKUP vs. DGET design decisions you'd better heed the Remarks section of the Excel Help Index topic for "DGET worksheet function". VLOOKUP wouldn't behave this way.
 
Upvote 0
is a DGET more efficient than a LOOKUP function? I'm currently using a VLOOKUP and it's just fine, but I thought a DGET may be quicker...but I could be totally wrong, so if you could clear that up, I'd appreciate it.

Yes, DGET would be faster but less flexible than e.g., VLOOKUP.

As far as my DGET, let's say I have 3 columns in my database with the following 3 rows:
{Name, Q1, Q2}
{Item1, 11, 22}
{Item2, 33, 44}

My formula: DGET(range,"Q1", ?? )...it works if the '??' is either a named range or a cell reference. The problem is that I don't want to have to have a 2 row criteria for each DGET, of which there are many. I'd like to use an array in the formula like {"Name";"Item2"}.


Alas, no constant arrays as {"Name";"Item2"} as criteria; Try:

DGET(range,"Q1", TRANSPOSE(E1:E2))

where E1 houses "Name" and E2 "Item2", it won't work.

Aladin
This message was edited by Aladin Akyurek on 2002-04-17 15:02
This message was edited by Aladin Akyurek on 2002-04-17 15:03
 
Upvote 0
for wats its worth Dget is cool if needed and most use common function insteatd of database function

D functions are a pig to get used to offfsetting 2 cells to refer to to get the correct result i feel is best. i get shot but straight d f i never suceeded with aladins comments i would shoot at and try..

else Dget... tey ozgrid.com my mate dave hawley has section on dget im sure and thats cool enought download wkbk with working example,,,


PLEASE be sure to post on her e this feed you commenst and reply so all excllers can see and enjoy..

Dget will return results wil...
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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
Back
Top