Tuesday, February 21, 2012

yogi_Lookup Max Value Of An Attribute In Another Sheet

Yogi Anand, D.Eng, P.E.                                      Google Spreadsheet                       www.energyefficientbuild.com

user ELM98 said:
How To Lookup A MAX Value In Another Sheet
I am working on a spreadsheet that keeps tracks of loans. On the first sheet is a list of the loans with information about who the loan is too, the loan amount, the payment amount, etc... The second tab keeps track of payments to loans and it has three columns, the loan that the payment is for, the date of the payment, and the payment amount.
On the first sheet I would like to add a column that shows the last payment date for each loan, but I am struggling with how to do this. The DMAX function looks like what I want, but I am struggling with what to pass in for the third parameter (the criteria array). On all the examples I see they have a group of cells setup for the criteria. The group of cells for the criteria how two rows, the first row has the name of the cells that they want to filter on, and the second row has the value that they want to filter on. However I do not want to setup a separate set of criteria cells for each loan. I want to just be able to have it get the loan name from the row it is on, and use that to lookup the last payment date for that loan. What is the best way to do this?
Thank You
--------------------------------------------
following is a solution to the problem