Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: DGET limitations...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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?


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •