Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 24 Oct 2015 01:16:58 +0200So 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)Tue, 28 Jul 2015 18:21:10 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/Comment by joshuaos for <p>Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...</p>
<p>I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...</p>
<pre><code>Date | Who
4/12/13 | Joshua
4/12/14 | Joshua
4/12/15 | Joshua
3/2/15 | James
</code></pre>
<p>Etcetera, with names and dates entered forever and the list getting longer.</p>
<p>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...</p>
<pre><code>A B
1 | Who | Last Review
2 | Joshua | =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}
</code></pre>
<p>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!</p>
<p>Anyone have any ideas? :)
Thank you all so much!</p>
<p>[edit]</p>
<p>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!</p>
<p><a href="/upfiles/14383013019209485.ods">council record.ods</a></p>
<p>This forum is amazing! Thank you all, I had nearly given up hope this was possible!
--Joshua</p>
<p>[edit]</p>
<p>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.</p>
<p><a href="/upfiles/14456423157157275.ods">council record.ods</a></p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53971#post-id-53971= IF (
$A5 <> "",
MATCH (
IF (
AND (
'advocacy log'.$B$3:$B$1003 = $A5,
'advocacy log'.$A$3:$A$1003 = $E5
),
$A$1003:$B$1003
),
'advocacy log'.$C$3:$C$1003
)
, ""
)
get's #N/A... good approach?Fri, 31 Jul 2015 06:17:12 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53971#post-id-53971Comment by joshuaos for <p>Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...</p>
<p>I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...</p>
<pre><code>Date | Who
4/12/13 | Joshua
4/12/14 | Joshua
4/12/15 | Joshua
3/2/15 | James
</code></pre>
<p>Etcetera, with names and dates entered forever and the list getting longer.</p>
<p>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...</p>
<pre><code>A B
1 | Who | Last Review
2 | Joshua | =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}
</code></pre>
<p>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!</p>
<p>Anyone have any ideas? :)
Thank you all so much!</p>
<p>[edit]</p>
<p>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!</p>
<p><a href="/upfiles/14383013019209485.ods">council record.ods</a></p>
<p>This forum is amazing! Thank you all, I had nearly given up hope this was possible!
--Joshua</p>
<p>[edit]</p>
<p>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.</p>
<p><a href="/upfiles/14456423157157275.ods">council record.ods</a></p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53946#post-id-53946Interestingly, I have determined that the cleanest solution, which doesn't work in Calc, DOES work in google sheets! It accepts A3 instead of "Joshua" in the {} syntax, generating the array dynamically and functioning perfectly.Thu, 30 Jul 2015 19:12:31 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53946#post-id-53946Answer by pierre-yves samyn for <p>Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...</p>
<p>I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...</p>
<pre><code>Date | Who
4/12/13 | Joshua
4/12/14 | Joshua
4/12/15 | Joshua
3/2/15 | James
</code></pre>
<p>Etcetera, with names and dates entered forever and the list getting longer.</p>
<p>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...</p>
<pre><code>A B
1 | Who | Last Review
2 | Joshua | =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}
</code></pre>
<p>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!</p>
<p>Anyone have any ideas? :)
Thank you all so much!</p>
<p>[edit]</p>
<p>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!</p>
<p><a href="/upfiles/14383013019209485.ods">council record.ods</a></p>
<p>This forum is amazing! Thank you all, I had nearly given up hope this was possible!
--Joshua</p>
<p>[edit]</p>
<p>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.</p>
<p><a href="/upfiles/14456423157157275.ods">council record.ods</a></p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?answer=53855#post-id-53855Hi
Pivot Table is the simplest solution, so the best IMO. Just for fun a (very) complicated formula solution.
I tried to explain in the spreadsheet itself.
[FindMax.ods](/upfiles/14381792969482935.ods)
[EDIT]
New version with formula for col F (I hope I have understood your request?)
[council recordPys.ods](/upfiles/14383472641780344.ods)
RegardsWed, 29 Jul 2015 16:16:01 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?answer=53855#post-id-53855Comment by joshuaos for <p>Hi</p>
<p>Pivot Table is the simplest solution, so the best IMO. Just for fun a (very) complicated formula solution.</p>
<p>I tried to explain in the spreadsheet itself.</p>
<p><a href="/upfiles/14381792969482935.ods">FindMax.ods</a></p>
<p>[EDIT]</p>
<p>New version with formula for col F (I hope I have understood your request?)
<a href="/upfiles/14383472641780344.ods">council recordPys.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53961#post-id-53961Okay, that is absolutely brilliant. It works perfectly. I don't completely understand it, but I've managed to implement it. Not only did you solve the question I asked, but you solved a problem I hadn't even thought to try and solve, that of creating the "current" list of who is actually here, which I thought fine to maintain manually. Fantastically done, man! I'm working on adapting it to the rest of the sheet now! Thank you!Fri, 31 Jul 2015 01:07:36 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53961#post-id-53961Comment by joshuaos for <p>Hi</p>
<p>Pivot Table is the simplest solution, so the best IMO. Just for fun a (very) complicated formula solution.</p>
<p>I tried to explain in the spreadsheet itself.</p>
<p><a href="/upfiles/14381792969482935.ods">FindMax.ods</a></p>
<p>[EDIT]</p>
<p>New version with formula for col F (I hope I have understood your request?)
<a href="/upfiles/14383472641780344.ods">council recordPys.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53963#post-id-53963What if I need to pull another piece of data out of the log? I need to pull another column out of the log, on that same row as the last date, while still matching the name. Even now having the date in question and the name I can't figure out how to have two criteria in the search. DMAX could do it, but only if we could dynamically generate the array, so I'm back to the same problem! If we could return the row in question, I could use offset. Any insight?Fri, 31 Jul 2015 01:49:50 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53963#post-id-53963Comment by joshuaos for <p>Hi</p>
<p>Pivot Table is the simplest solution, so the best IMO. Just for fun a (very) complicated formula solution.</p>
<p>I tried to explain in the spreadsheet itself.</p>
<p><a href="/upfiles/14381792969482935.ods">FindMax.ods</a></p>
<p>[EDIT]</p>
<p>New version with formula for col F (I hope I have understood your request?)
<a href="/upfiles/14383472641780344.ods">council recordPys.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=59922#post-id-59922So we've been really using that sheet, and have found a bug in the F column, which happens when two entries on the advocacy log tab have the same date (which happens frequently), as shown in the file I am editing the question to add. I can barely understand that brilliant formula you built and hoped you could chime in with a fix for this bug, please. :)Sat, 24 Oct 2015 01:16:58 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=59922#post-id-59922Comment by joshuaos for <p>Hi</p>
<p>Pivot Table is the simplest solution, so the best IMO. Just for fun a (very) complicated formula solution.</p>
<p>I tried to explain in the spreadsheet itself.</p>
<p><a href="/upfiles/14381792969482935.ods">FindMax.ods</a></p>
<p>[EDIT]</p>
<p>New version with formula for col F (I hope I have understood your request?)
<a href="/upfiles/14383472641780344.ods">council recordPys.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53982#post-id-53982You are absolutely fantastic. It works! I've been beating my head against this wall for ages, and you've completely solved it. I can't thank you enough!Fri, 31 Jul 2015 15:57:32 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53982#post-id-53982Comment by joshuaos for <p>Hi</p>
<p>Pivot Table is the simplest solution, so the best IMO. Just for fun a (very) complicated formula solution.</p>
<p>I tried to explain in the spreadsheet itself.</p>
<p><a href="/upfiles/14381792969482935.ods">FindMax.ods</a></p>
<p>[EDIT]</p>
<p>New version with formula for col F (I hope I have understood your request?)
<a href="/upfiles/14383472641780344.ods">council recordPys.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53959#post-id-53959That is very interesting, and I think it solves my problem... I am still working on understanding this solution...Fri, 31 Jul 2015 00:22:06 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53959#post-id-53959Answer by karolus for <p>Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...</p>
<p>I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...</p>
<pre><code>Date | Who
4/12/13 | Joshua
4/12/14 | Joshua
4/12/15 | Joshua
3/2/15 | James
</code></pre>
<p>Etcetera, with names and dates entered forever and the list getting longer.</p>
<p>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...</p>
<pre><code>A B
1 | Who | Last Review
2 | Joshua | =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}
</code></pre>
<p>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!</p>
<p>Anyone have any ideas? :)
Thank you all so much!</p>
<p>[edit]</p>
<p>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!</p>
<p><a href="/upfiles/14383013019209485.ods">council record.ods</a></p>
<p>This forum is amazing! Thank you all, I had nearly given up hope this was possible!
--Joshua</p>
<p>[edit]</p>
<p>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.</p>
<p><a href="/upfiles/14456423157157275.ods">council record.ods</a></p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?answer=53798#post-id-53798set up `→Data→Pivottable...`
drag&drop `Who` into Rowfield, drag&drop `Date` into Datafield, change Aggregatfunction from `sum` to `max` and hit `ok`
or change your Formula to:
=DMAX('review_log'.$A$1:$B$1003,"Date",A1:A2)Tue, 28 Jul 2015 19:03:09 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?answer=53798#post-id-53798Comment by joshuaos for <p>set up <code>→Data→Pivottable...</code> </p>
<p>drag&drop <code>Who</code> into Rowfield, drag&drop <code>Date</code> into Datafield, change Aggregatfunction from <code>sum</code> to <code>max</code> and hit <code>ok</code></p>
<p>or change your Formula to:</p>
<pre><code>=DMAX('review_log'.$A$1:$B$1003,"Date",A1:A2)
</code></pre>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53815#post-id-53815Sorry, I still don't get it. Do I use the review_log as the data set for the pivot table? How does that interact with the function that pulls the data from the log?Tue, 28 Jul 2015 21:00:34 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53815#post-id-53815Comment by karolus for <p>set up <code>→Data→Pivottable...</code> </p>
<p>drag&drop <code>Who</code> into Rowfield, drag&drop <code>Date</code> into Datafield, change Aggregatfunction from <code>sum</code> to <code>max</code> and hit <code>ok</code></p>
<p>or change your Formula to:</p>
<pre><code>=DMAX('review_log'.$A$1:$B$1003,"Date",A1:A2)
</code></pre>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53817#post-id-53817> however it works ONLY for the first...
thats the reason for using `Pivottable` start from one cell in Source.Tue, 28 Jul 2015 21:29:10 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53817#post-id-53817Comment by joshuaos for <p>set up <code>→Data→Pivottable...</code> </p>
<p>drag&drop <code>Who</code> into Rowfield, drag&drop <code>Date</code> into Datafield, change Aggregatfunction from <code>sum</code> to <code>max</code> and hit <code>ok</code></p>
<p>or change your Formula to:</p>
<pre><code>=DMAX('review_log'.$A$1:$B$1003,"Date",A1:A2)
</code></pre>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53813#post-id-53813So, I did try that (making the array A1:A2) and it does work... however it works ONLY for the first name. There will be a whole list of names which need to pull data from the log, and so it needs to work for all of them....
The other solution (Pivottable)... Hmm... a little confused, but trying...Tue, 28 Jul 2015 20:56:33 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53813#post-id-53813Answer by Alex Kemp for <p>Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...</p>
<p>I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...</p>
<pre><code>Date | Who
4/12/13 | Joshua
4/12/14 | Joshua
4/12/15 | Joshua
3/2/15 | James
</code></pre>
<p>Etcetera, with names and dates entered forever and the list getting longer.</p>
<p>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...</p>
<pre><code>A B
1 | Who | Last Review
2 | Joshua | =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}
</code></pre>
<p>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!</p>
<p>Anyone have any ideas? :)
Thank you all so much!</p>
<p>[edit]</p>
<p>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!</p>
<p><a href="/upfiles/14383013019209485.ods">council record.ods</a></p>
<p>This forum is amazing! Thank you all, I had nearly given up hope this was possible!
--Joshua</p>
<p>[edit]</p>
<p>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.</p>
<p><a href="/upfiles/14456423157157275.ods">council record.ods</a></p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?answer=53800#post-id-53800> "...names and dates entered forever
> and the list getting longer."
You are using the wrong kind of app for this kind of data. You need to use a DB (database) App, else one day you will likely run out of memory.Tue, 28 Jul 2015 19:11:06 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?answer=53800#post-id-53800Comment by joshuaos for <blockquote>
<p>"...names and dates entered forever
and the list getting longer."</p>
</blockquote>
<p>You are using the wrong kind of app for this kind of data. You need to use a DB (database) App, else one day you will likely run out of memory.</p>
https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53812#post-id-53812It's true that a database would be better... however the rate at which this will grow is very slowly... maybe one or two lines per month, so I figure this will work for a damn long time, neh? (that is, if I can figure out this formula!)Tue, 28 Jul 2015 20:54:18 +0200https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/?comment=53812#post-id-53812