Excel 2007 - Table defined, DGet based on criteria

Posted on 2012-08-21
Last Modified: 2012-08-27
Hello all....

I have a table in Access 2007 (Yes defined as an Excel table).  I am wanting to use the Excel Function DGet but am having trouble
My Table has the Following Headers

Classification ----- Description ------ Description - Extended ------- Date ------ Year ------ Amount

I want to be able the automate a worksheet where I pull the most recent data from the Table
In cell M14 I have =DMAX(Table4[#All],Table4[[#Headers],[Date]],'Balance Sheet'!D:D) which get the last date of any input data.

So in Cells C1 to H1 I copied the Header values
Then in D2 I put '=Cash and Cash equivalents
in F2 I put ="="&(TEXT(M14,"m/d/yyyy"))

I know there is a line in that date that has that description.

Now then in cell M16 I have put =DGET(Table4[#All],Table4[#Headers],D1:F2)

But all I get is #Value!

Any thoughts on what I am doing wrong (Note I have tried just typing '=6/30/2012 and also tried ="="&datevalue(TEXT(M14,"m/d/yyyy"))... same result

Can you not use that type criteria with an Excel Table?  or have I just written it wrong?
Question by:wlwebb
    LVL 24

    Expert Comment

    PLease could we have a sample workbook with a little dummy data.
    this should be simple enough with a sample dataset.

    Author Comment

    The Barman
    Sure here it is
    LVL 24

    Expert Comment

    OK, for financial data it is best to use Sum Functions rather than DGET.

    I have attached a file with the first "SUMIFS" which should get you started.
    It eliminates the need for a criteria page too.
    Have a look, see if it meets requirements.

    BTW, am still looking to see what the problem is with the DGET.
    LVL 24

    Accepted Solution

    OK, after much head scratching :)  ....

    The Field criteria is the field "header" you are after (Amount)
    And there is no need to fudge the date in the criteria.
    See attached for corrected DGET function.

    Author Closing Comment

    THANKS!!! missed that one

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now