DGET limitations...

[msvec]

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.

[lenze]

Can't you name your criteria range and use that name in the DGET formula. it works for me.

[Aladin Akyurek]

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?

[msvec]

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

[Mark W.]

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.

[Aladin Akyurek]

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 ]

[Jack in the UK]

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