asked on

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?

Steve

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

wlwebb

ASKER

The Barman
Sure here it is
SEC-10-QK-Historical---Copy.xlsx

Steve

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

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

THANKS!!! missed that one