[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

Excel 2007 - Table defined, DGet based on criteria

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?
0
wlwebb
Asked:
wlwebb
  • 3
  • 2
1 Solution
 
SteveCommented:
PLease could we have a sample workbook with a little dummy data.
this should be simple enough with a sample dataset.
0
 
wlwebbAuthor Commented:
The Barman
Sure here it is
SEC-10-QK-Historical---Copy.xlsx
0
 
SteveCommented:
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.
SEC-10-QK-Historical---Copy.xlsx
0
 
SteveCommented:
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.
SEC-10-QK-Historical---Copy.xlsx
0
 
wlwebbAuthor Commented:
THANKS!!! missed that one
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now