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.
Bookmarks