Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 27 Oct 2015 14:35:54 +0100bug in tricky array formulahttps://ask.libreoffice.org/en/question/60067/bug-in-tricky-array-formula/Hello all!
So, with the help of this forum a couple of months ago, I got this epic and tricky spreadsheet done with some very complex formulas, which had stumped me forever until someone brilliant used array formulas to solve my problem.
Now, in using the sheet, there is a bug and I am at a complete loss to get it to work right! Please please you formula gurus out there, help me get this bug figured out.
(I am on LibreOffice Calc 4.2.8.2 in English under linux)
The spreadsheet is here attached, and the problem is in the 'current' tab in the F column. The formula pulls data from the 'advocacy log' tab and is supposed to pull the name of the most recent advocate for the person in question (named in the A column of the current sheet on that same line). It works until two entries have the SAME DATE, then as you can see in the example data, it doesn't work (current.F9 should show as Joshua).
[council record.ods](/upfiles/14459528694904954.ods)
I am so lost in this formula, your help is deeply appreciated.
Thanks, JoshuajoshuaosTue, 27 Oct 2015 14:35:54 +0100https://ask.libreoffice.org/en/question/60067/So close to DMAX solution...https://ask.libreoffice.org/en/question/53794/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](/upfiles/14383013019209485.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](/upfiles/14456423157157275.ods)joshuaosTue, 28 Jul 2015 18:21:10 +0200https://ask.libreoffice.org/en/question/53794/