DGET, DMAX, etc. no longer accept criteria with dynamic references 2007 vs 2010?

[NLawrence]

Hi,

Simply put excel database functions (DGET, DMAX, DCOUNT, etc.) no longer seem to accept criteria functions with dynamic references. This is a change seems to have taken place between excel 2007 and 2010.

I created a spread sheet in excel 2007 that finds matches between items in a database. The spreadsheet had potential for multiple results for each match up and found the record with the latest date. The issue I have is that the spreadsheet no longer works in excel 2010.

Below is an example of the output it would produce although simplified.

Apple	Banana	Taste
A1	B1	Good
A2	B2	Nice
A2	B1	Ok
A1	B2	Bad

	B1	B2
A1	Good	Bad
A2	Ok	Nice

The criteria formulas used were

Banana Calc
='Data Source'!$B2=INDEX(Calcs!$2:$2,COLUMN())

Apple Calc
='Data Source'!$C2=INDEX(Calcs!$E:$E,ROW())

Where calcs!$2:$2 is the header row of the matrix and calcs!$e:$e is the first column in the matrix. The spread sheet was created in excel 2007 and worked fine, however when opened in 2010 no longer finds any matches with the given criteria. With a bit of playing around I cannot get any of the database functions to work with any dynamic reference.

Has anyone else encountered this and got it to work, or know of any work arounds?

Thanks,
Nathan.

[NLawrence]

Hi,

Due to lack of response I have uploaded a .xlsx demonstrating what I am talking about along with the result from excel 2007 and 2010. Both screen shots are from the same spread sheet running on different machines.

Please note that all formulas inside the matrix are identical, which is what I am trying to achieve.

If no one knows any way around this problem can someone suggest how to go about contacting Microsoft regarding this issue?

Thanks,
Nathan.

2007 result.JPG
2010 Result.JPG