Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

So close to DMAX solution...

Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...

I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...

Date | Who 4/12/13 | Joshua 4/12/14 | Joshua 4/12/15 | Joshua 3/2/15 | James

Etcetera, with names and dates entered forever and the list getting longer.

On another sheet, I enter in the first column just the name, in the test case, Joshua... and I need to get the date of the LAST (chronologically) entry for that name. The closest I've come uses DMAX... A B 1 | Who | Last Review 2 | Joshua | =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}

Now right now, this DMAX works! However, the name "Joshua" is hard coded into it. I need this function to work dynamically so that "Joshua" in the criteria array is replaced with the value of A2... I have tried simply putting A2 there, "A2", =A2, "=A2", using the cell function... and I can't get anything to work!

Anyone have any ideas? :) Thank you all so much!

So close to DMAX solution...

Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...

I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...

Date          | Who
4/12/13    |   Joshua
4/12/14    |   Joshua
4/12/15    |   Joshua
3/2/15      |   James

James

Etcetera, with names and dates entered forever and the list getting longer.

On another sheet, I enter in the first column just the name, in the test case, Joshua... and I need to get the date of the LAST (chronologically) entry for that name. The closest I've come uses DMAX... DMAX...

A                    B
1  | Who           |   Last Review
2  |  Joshua       |   =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}

=DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}

Now right now, this DMAX works! However, the name "Joshua" is hard coded into it. I need this function to work dynamically so that "Joshua" in the criteria array is replaced with the value of A2... I have tried simply putting A2 there, "A2", =A2, "=A2", using the cell function... and I can't get anything to work!

Anyone have any ideas? :) Thank you all so much!

So close to DMAX solution...

Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...

I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...

Date          | Who
4/12/13    |   Joshua
4/12/14    |   Joshua
4/12/15    |   Joshua
3/2/15      |   James

Etcetera, with names and dates entered forever and the list getting longer.

On another sheet, I enter in the first column just the name, in the test case, Joshua... and I need to get the date of the LAST (chronologically) entry for that name. The closest I've come uses DMAX...

A                    B
1  | Who           |   Last Review
2  |  Joshua       |   =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}

Now right now, this DMAX works! However, the name "Joshua" is hard coded into it. I need this function to work dynamically so that "Joshua" in the criteria array is replaced with the value of A2... I have tried simply putting A2 there, "A2", =A2, "=A2", using the cell function... and I can't get anything to work!

Anyone have any ideas? :) Thank you all so much!

[edit]

Thanks to a brilliant function by pierre-yves samyn, I am so so close to having this document fully functional! The problem is I need to pull another piece of data out along with the date! Here is the actual spreadsheet I'm stuck on for clarity. It's the first sheet ("current"), column F is all that's left, with the solutions for C and E already found!

council record.ods

This forum is amazing! Thank you all, I had nearly given up hope this was possible! --Joshua

So close to DMAX solution...

Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...

I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...

Date          | Who
4/12/13    |   Joshua
4/12/14    |   Joshua
4/12/15    |   Joshua
3/2/15      |   James

Etcetera, with names and dates entered forever and the list getting longer.

On another sheet, I enter in the first column just the name, in the test case, Joshua... and I need to get the date of the LAST (chronologically) entry for that name. The closest I've come uses DMAX...

A                    B
1  | Who           |   Last Review
2  |  Joshua       |   =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}

Now right now, this DMAX works! However, the name "Joshua" is hard coded into it. I need this function to work dynamically so that "Joshua" in the criteria array is replaced with the value of A2... I have tried simply putting A2 there, "A2", =A2, "=A2", using the cell function... and I can't get anything to work!

Anyone have any ideas? :) Thank you all so much!

[edit]

Thanks to a brilliant function by pierre-yves samyn, I am so so close to having this document fully functional! The problem is I need to pull another piece of data out along with the date! Here is the actual spreadsheet I'm stuck on for clarity. It's the first sheet ("current"), column F is all that's left, with the solutions for C and E already found!

council record.ods

This forum is amazing! Thank you all, I had nearly given up hope this was possible! --Joshua

So close to DMAX solution...

Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...

I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...

Date          | Who
4/12/13    |   Joshua
4/12/14    |   Joshua
4/12/15    |   Joshua
3/2/15      |   James

Etcetera, with names and dates entered forever and the list getting longer.

On another sheet, I enter in the first column just the name, in the test case, Joshua... and I need to get the date of the LAST (chronologically) entry for that name. The closest I've come uses DMAX...

A                    B
1  | Who           |   Last Review
2  |  Joshua       |   =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}

Now right now, this DMAX works! However, the name "Joshua" is hard coded into it. I need this function to work dynamically so that "Joshua" in the criteria array is replaced with the value of A2... I have tried simply putting A2 there, "A2", =A2, "=A2", using the cell function... and I can't get anything to work!

Anyone have any ideas? :) Thank you all so much!

[edit]

Thanks to a brilliant function by pierre-yves samyn, I am so so close to having this document fully functional! The problem is I need to pull another piece of data out along with the date! Here is the actual spreadsheet I'm stuck on for clarity. It's the first sheet ("current"), column F is all that's left, with the solutions for C and E already found!

council record.ods

This forum is amazing! Thank you all, I had nearly given up hope this was possible! --Joshua

[edit]

So in using this sheet we've found a bug, which happens when two entries on the advocacy log page have the same date. As shown in this file, the F9 cell on the 'current' tab should read Joshua, based on the entries in the 'advocacy log' tab. I would really appreciate any solutions you can come up with, I'm at a loss.

council record.ods