Database Functions Combine DGET and DMAX?

[karabiner8]

A B C
1 Date Date Answer
2 >=1/2/11 <=28/2/11 No
3 >=1/2/11 <=28/2/11 Yes
4
5
6
7 Date Answer
8 Jan 14, 2011 Yes
9 Jan 14, 2011 No
10 Feb 2, 2011 No
11 Feb 17, 2011 Yes
12 Feb 18, 2011 (Empty)<BLANK>

Hi all. I'd like to get the value in the Answer column of my database based on three criteria. The date must be in February, it must be the most recent date and the Answer column cannot be blank (must be either "Yes" or "No").

With my data laid out as above (simplified) with criteria in A1:C3 and database in A7:B12 I can use DMAX(A7:B12,"Date",A1:C3) to correctly return Feb 17, 2011 (because Feb 18, 2011 has a blank in the Answer column).

My question is how do I get the value in the Answer column ("Yes", in this case)? I figure there must be a way to use DGET but I cannot figure out how to set the criteria.

BTW, I know how to do this with SUMPRODUCT but I'm trying to stick to the database functions for this particular spreadsheet.

Any help would be most appreciated as none of my searching has turned up anything.

 

[Aladin Akyurek]

...

I'd like to get the value in the Answer column of my database based on three criteria. The date must be in February, it must be the most recent date and the Answer column cannot be blank (must be either "Yes" or "No")...

Is this what you want?

Return the max date (most recent date) that falls in the month/year of Feb/2011, associate with a non-blank/empty Answer value.

 

[karabiner8]

Yes Aladin Akyurek, that is what I am trying to achieve.

 

[AlphaFrog]

Does DGET have a problem with the d/m/yyyy date format in its criteria? I ask this because I tried it with using m/d/yyyy in the criteria and it seemed to work fine.

Excel Workbook
	A	B	C	D	E
1	Date	Date	Answer	*	Feb 17, 2011
2	>=2/1/2011		Yes	*	Yes
3	*	*	*	*	*
4	*	*	*	*	*
5	*	*	*	*	*
6	*	*	*	*	*
7	Date	Answer	*	*	*
8	Jan 14, 2011	Yes	*	*	*
9	Jan 14, 2011	No	*	*	*
10	Feb 2, 2011	No	*	*	*
11	Feb 17, 2011	Yes	*	*	*
12	Feb 18, 2011	*	*	*	*
Sheet

Note: DGET will return an error if there is more than one match of all the criteria.

 

[karabiner8]

Hi AlphaFrog,

Both date formats worked for me. Thanks for the Excel-to-Web, I'll definitely check that out.

Unfortunately your solution doesn't appear to work. Try changing the value in cell B11 to a "No" and you'll get an error in the DGET function and DMAX will return Jan 00, 1900 (i.e. serial # 0).

The criteria you set up seems to simply find a "Yes" in the month of February. What I need is to report the value in the Answer column ("Yes" or "No") of the most recent (i.e. maximum) day in February as long as the value in the Answer column is not empty.

Any other ideas?

 

[AlphaFrog]

DGET will return an error if there is more than one match.

Would you want to use the DMAX to get the date and then use a VLOOKUP to return data from other columns oin the same row as the DMAX date?

Excel Workbook
	A	B	C	D	E
1	Date	Date	Answer		Feb 17, 2011
2	>=2/1/2011				No
3
4
5
6
7	Date	Answer
8	Jan 14, 2011	Yes
9	Jan 14, 2011	No
10	Feb 2, 2011	No
11	Feb 17, 2011	No
12	Feb 18, 2011
Sheet

 

[karabiner8]

AlphaFrog, I'm sorry but your solution is not what I am trying to achieve. Thanks anyway.

 

[AlphaFrog]

Edited post #6

 

[AlphaFrog]

Or this if you really want to use DGET...

Excel Workbook
	A	B	C	D	E
1	Date	Date	Answer		Date
2	>=2/1/2011				Feb 17, 2011
3					No
4
5
6
7	Date	Answer
8	Jan 14, 2011	Yes
9	Jan 14, 2011	No
10	Feb 2, 2011	No
11	Feb 17, 2011	No
12	Feb 18, 2011
Sheet

 

[karabiner8]

YEEEEEESSSSSSSS!!!!!!!!!!

Thank you so much Alpha Frog. It works perfectly. You just made my day.