I'm trying to find a way to dynamically adust the criteria used in calls to db functions like DMAX and DMIN by specifying the criterion value to be obtained from anotheer cell in the worksheet. To illustrate what I mean, please try this:
Start with a blank Excel 2007 worksheet.
Enter the word Test in cells A1, D1 and A7 and the numbers 1,2 and 3 into cells A8, A9 & A10 respectively.
Enter >=3 in A2 and =">=a5" in D2
Click cell A4 and enter the following into the formula box:=DMIN(A$7:A$10, 1, A$1:A$2).
A4 now correctly displays the value 3. However, what I'd like to know is the correct criteria expression to say that it should pick up the value to use from another cell.
So
Enter 2 in A5.
Enter =DMIN(A$7:A$10, 1, D$1:D$2) in A6.
A6 now displays 0, which is not what I want. I want the value to be used in the criterion to be picked up from A5. So, my question is, what is the correct way to tell the criterion to get the expression value from the contents of another cell?
=">=a5" will return the string >=a5 when you want it to return the result of the cell not the cell address.
Remove the cell address from the quotes:
=">=" & A5
This now will return >= and the value in A5, in this case 2; >=2.
This then can be properly interpreted by the DMIN.