Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
David, A follow up to my first reply. I wanted to investigate a bit more to see if I could produce a macro that actually incremented values in column B based on the corresponding cell in column D being changed. This works for me incrementing the value in col B by +1 when the value in column D on the same row is changed. Please note, it only works for the first sheet of the workbook. I expect it could be made to work for the current sheet but have not investigated how to do that. Changing the order of the workbook tabs has no effect on which tab is recognized as the first one. In addition to the reference I named in my first post the information from this second reference was instrumental in making a macro that only responded to changes in cells in column D. https://docs.oracle.com/cd/E19064-01/so6/817-1826-10/817-1826-10.pdf pg. 119 and the macro... Sub SheetChange(oEvent) Dim rowNum, colNum as Integer Dim oDoc, oSheet, oCell as Object oDoc = StarDesktop.CurrentComponent oSheet = oDoc.Sheets(0) if oEvent.CellAddress.Column = 3 then colNum = oEvent.CellAddress.Column rowNum = oEvent.CellAddress.Row oCell = oSheet.getCellByPosition(colNum - 2, rowNum) oCell.Value = oCell.Value + 1 EndIf End Sub On Tue, Dec 10, 2019 at 11:50 PM zed wrote: > Hi! > > Using LibreOffice v6.0.7.3 on Linux Mint v19.2 Mate > > I have a spreadsheeet in Calc which records data of reactions to a > particular performer. It is in the form: It has Columns A - G. > > A - Name, fformatted as Text (records thre name of the persons doing the > reaction) > B - No, formatted number general (records number of times this > reactors has reacted to the performer) > C - First, formatted DD/MM/YY (records the first date the reactor reaacted > to this performer) > D - Last, formatted DD/MM.YY (records last date reactor reacted to this > performer) > E - Days, formatted =NOW()-D3 (records number of days since last reaction) > F - Weeks, formatted =E3/7 (records number of weeks since last reaction) > G - Months, formatted =F3/4 (records number of months since last reaction. > reaction) > > Is there a formula that I can enter in Column B which will increase > incrementally increase the figure by 1, please? > > Regards from New Zealand on a very warm summer evening. > > David > -- > Zed > "To use the term blind faith, is to use an adjective needlessly." Julian > Ruck > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > > -- Alan Boba CISSP, CCENT, ITIL v3 Foundations 2011 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
Generally, The following should work, but spelling or spacing differences won't be ignored. any difference in the name will be treated as a new reviewer. IF(A1=A2,B1+1,1) On Tue, Dec 10, 2019 at 10:51 PM zed wrote: > Hi! > > Using LibreOffice v6.0.7.3 on Linux Mint v19.2 Mate > > I have a spreadsheeet in Calc which records data of reactions to a > particular performer. It is in the form: It has Columns A - G. > > A - Name, fformatted as Text (records thre name of the persons doing the > reaction) > B - No, formatted number general (records number of times this > reactors has reacted to the performer) > C - First, formatted DD/MM/YY (records the first date the reactor reaacted > to this performer) > D - Last, formatted DD/MM.YY (records last date reactor reacted to this > performer) > E - Days, formatted =NOW()-D3 (records number of days since last reaction) > F - Weeks, formatted =E3/7 (records number of weeks since last reaction) > G - Months, formatted =F3/4 (records number of months since last reaction. > reaction) > > Is there a formula that I can enter in Column B which will increase > incrementally increase the figure by 1, please? > > Regards from New Zealand on a very warm summer evening. > > David > -- > Zed > "To use the term blind faith, is to use an adjective needlessly." Julian > Ruck > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
Hi David, As I understand it you have values in column B and dates in column D. When a new date is entered in a cell in column D or an existing date is changed you want the cell on the corresponding row in column B to increase by 1. e.g. B1 = 5, D1 = May 5, 2019 change D1 and B1 becomes 6. - or - e.g. B3 = 26, D3 = blank set D3 = Feb 3, 2017 and B3 becomes 27 The root of an answer is here, https://ask.libreoffice.org/en/question/84677/which-event-macro-on-spreadsheet-contents-change/ Provided by Peter Williams. The macro sample would need modification but seems to clearly demonstrate the capability you wish to implement. Sub SheetChange(oEvent) MsgBox "Column is " & oEvent.CellAddress.Column MsgBox "Row is " & oEvent.CellAddress.Row End Sub On Thu, Dec 12, 2019 at 2:13 PM Johnny Rosenberg wrote: > Den tors 12 dec. 2019 kl 05:38 skrev zed : > > > Brian Barker wrote: > > > > > At 17:49 11/12/2019 +1300, David Noname wrote: > > > > I have a spreadsheet in Calc ... It has Columns ... B - No, formatted > > > > number general ... > > > > > > > > Is there a formula that I can enter in Column B which will increase > > > > incrementally increase the figure by 1, please? > > > > > > I think you are saying that you want values in column B to be one > greater > > > than they are. That's a contradiction. If the value in Bn is four, you > > > want it to be five. But that means it's no longer four. And if it's now > > > five, how would any formula know you do not want it to be six? And so > on? > > > > Thank you for your prompt reply, Brian. It is much appreciated > > > > Perhaps I didn't explain myself clearly enough. > > > > In simple terms, I want to know whether there is any way that when I > enter > > a > > new date in Column D it will automaticall increase the current value in > > Column B by 1. I cannot think of a solution - but then I only use Calc > for > > simple things - but was hoping that there is a solution. If there > isn't, I > > will just have to try and remember to manually increase the value of > Column > > B every time I enter a new date in Column D. > > > > Can you explain it again, but this time also mention rows? > Do you mean that a certain cell in column B, say B1, should increase its > value when you enter a new date in a previously empty cell in column D? Is > that cell always direclty below the last used row? > > Do you mean that when you enter a date in the D column, the cell at the > same row in column B should be the cell above it + 1? > > > > > > > David > > -- > > Zed > > Smoking kills. If you're killed, you've lost a very important part of > your > > life. > > > > -- > > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > > Problems? > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > > Posting guidelines + more: > https://wiki.documentfoundation.org/Netiquette > > List archive: https://listarchives.libreoffice.org/global/users/ > > Privacy Policy: https://www.documentfoundation.org/privacy > > > > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > -- Alan Boba CISSP, CCENT, ITIL v3 Foundations 2011 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
Den tors 12 dec. 2019 kl 05:38 skrev zed : > Brian Barker wrote: > > > At 17:49 11/12/2019 +1300, David Noname wrote: > > > I have a spreadsheet in Calc ... It has Columns ... B - No, formatted > > > number general ... > > > > > > Is there a formula that I can enter in Column B which will increase > > > incrementally increase the figure by 1, please? > > > > I think you are saying that you want values in column B to be one greater > > than they are. That's a contradiction. If the value in Bn is four, you > > want it to be five. But that means it's no longer four. And if it's now > > five, how would any formula know you do not want it to be six? And so on? > > Thank you for your prompt reply, Brian. It is much appreciated > > Perhaps I didn't explain myself clearly enough. > > In simple terms, I want to know whether there is any way that when I enter > a > new date in Column D it will automaticall increase the current value in > Column B by 1. I cannot think of a solution - but then I only use Calc for > simple things - but was hoping that there is a solution. If there isn't, I > will just have to try and remember to manually increase the value of Column > B every time I enter a new date in Column D. > Can you explain it again, but this time also mention rows? Do you mean that a certain cell in column B, say B1, should increase its value when you enter a new date in a previously empty cell in column D? Is that cell always direclty below the last used row? Do you mean that when you enter a date in the D column, the cell at the same row in column B should be the cell above it + 1? > > David > -- > Zed > Smoking kills. If you're killed, you've lost a very important part of your > life. > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
At 17:35 12/12/2019 +1300, David Noname wrote: Thank you for your prompt reply, Brian. It is much appreciated No probs! Perhaps I didn't explain myself clearly enough. In simple terms, I want to know whether there is any way that when I enter a new date in Column D it will automatically increase the current value in Column B by 1. I guessed that you meant something like this - but no, it wasn't clearly stated. If you could achieve what you ask, the result would be hopelessly fragile. We all make mistakes. Suppose that you enter your new date incorrectly. The value in column B will be incremented. Then you need to enter the correct date, whereupon the value in column B will be incremented again. How would you then correct the situation? If you tried to correct the value in column B manually, you would overwrite whatever formula there was doing the trick for you in the first place - and disable it. And what if you entered a correct new date but mistakenly for the wrong person? There is, I think, a solution, and that is to separate data entry from the display you actually want to see. Construct a separate area - quite possibly on a separate sheet of the spreadsheet - for your data entry. This will have a row or column for each of your named "reactors", and you will add dates here instead of in the results sheet directly. Note that you *add* dates, so that you assemble a list of dates, rather than overwriting a single entry with the most recent date. Now all you need to do in column B of your display table is to construct a formula which counts the number of dates currently listed for that person in the data entry area. And in column D of the display table you will similarly construct a formula which picks up the most recent date from the data entry area. With this scheme, any mistaken entries can be corrected with the display table always picking up the corrected values. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
Johnny Rosenberg wrote: > Den ons 11 dec. 2019 kl 05:51 skrev zed : > > > Hi! > > > > Using LibreOffice v6.0.7.3 on Linux Mint v19.2 Mate > > > > I have a spreadsheeet in Calc which records data of reactions to a > > particular performer. It is in the form: It has Columns A - G. > > > > A - Name, fformatted as Text (records thre name of the persons doing the > > reaction) B - No, formatted number general (records number of times this > > reactors has reacted to the performer) C - First, formatted DD/MM/YY > > (records the first date the reactor reaacted to this performer) D - > > Last, formatted DD/MM.YY (records last date reactor reacted to this > > performer) E - Days, formatted =NOW()-D3 (records number of days since > > last reaction) F - Weeks, formatted =E3/7 (records number of weeks since > > last reaction) G - Months, formatted =F3/4 (records number of months > > since last reaction. reaction) > > > > Is there a formula that I can enter in Column B which will increase > > incrementally increase the figure by 1, please? > > > > Regards from New Zealand on a very warm summer evening. > > > > David -- Zed > I'm not sure I understand what you are asking for here. So in column A, > there are names, as you said, and in column B there's a number that tells > you how many times the name in Column A appeared so far? Then I guess the > following would work: B1: =IF(A1="";"";COUNTIF(A$1:A1;A1)) Click and drag > the formula downwards. Now, for instance B17 looks like this: > =IF(A17="";"";COUNTIF(A$1:A17;A17)) The IF is just to not display a zero > when nothing is entered into the corresponding A line. Thank you for your quick response. Unfortunately that didn't work. That was probably because I didn't explain myself carefully enough. Have a look at my answer to Brian Barker, where I try to be clearer in what I'm trying to achieve. If there is a solution I will think all my Christmases have come at once! But I am resigned to the fact that there probably isn't. David -- Zed "I have often regretted my speech, never my silence." Xenocrates (396-314 B.C.) -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
Brian Barker wrote: > At 17:49 11/12/2019 +1300, David Noname wrote: > > I have a spreadsheet in Calc ... It has Columns ... B - No, formatted > > number general ... > > > > Is there a formula that I can enter in Column B which will increase > > incrementally increase the figure by 1, please? > > I think you are saying that you want values in column B to be one greater > than they are. That's a contradiction. If the value in Bn is four, you > want it to be five. But that means it's no longer four. And if it's now > five, how would any formula know you do not want it to be six? And so on? Thank you for your prompt reply, Brian. It is much appreciated Perhaps I didn't explain myself clearly enough. In simple terms, I want to know whether there is any way that when I enter a new date in Column D it will automaticall increase the current value in Column B by 1. I cannot think of a solution - but then I only use Calc for simple things - but was hoping that there is a solution. If there isn't, I will just have to try and remember to manually increase the value of Column B every time I enter a new date in Column D. David -- Zed Smoking kills. If you're killed, you've lost a very important part of your life. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
Den ons 11 dec. 2019 kl 05:51 skrev zed : > Hi! > > Using LibreOffice v6.0.7.3 on Linux Mint v19.2 Mate > > I have a spreadsheeet in Calc which records data of reactions to a > particular performer. It is in the form: It has Columns A - G. > > A - Name, fformatted as Text (records thre name of the persons doing the > reaction) > B - No, formatted number general (records number of times this > reactors has reacted to the performer) > C - First, formatted DD/MM/YY (records the first date the reactor reaacted > to this performer) > D - Last, formatted DD/MM.YY (records last date reactor reacted to this > performer) > E - Days, formatted =NOW()-D3 (records number of days since last reaction) > F - Weeks, formatted =E3/7 (records number of weeks since last reaction) > G - Months, formatted =F3/4 (records number of months since last reaction. > reaction) > > Is there a formula that I can enter in Column B which will increase > incrementally increase the figure by 1, please? > > Regards from New Zealand on a very warm summer evening. > > David > -- > Zed > I'm not sure I understand what you are asking for here. So in column A, there are names, as you said, and in column B there's a number that tells you how many times the name in Column A appeared so far? Then I guess the following would work: B1: =IF(A1="";"";COUNTIF(A$1:A1;A1)) Click and drag the formula downwards. Now, for instance B17 looks like this: =IF(A17="";"";COUNTIF(A$1:A17;A17)) The IF is just to not display a zero when nothing is entered into the corresponding A line. Kind regards Johnny Rosenberg > "To use the term blind faith, is to use an adjective needlessly." Julian > Ruck > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LibreOffice Calc - Date - increase number times...
At 17:49 11/12/2019 +1300, David Noname wrote: I have a spreadsheet in Calc ... It has Columns ... B - No, formatted number general ... Is there a formula that I can enter in Column B which will increase incrementally increase the figure by 1, please? I think you are saying that you want values in column B to be one greater than they are. That's a contradiction. If the value in Bn is four, you want it to be five. But that means it's no longer four. And if it's now five, how would any formula know you do not want it to be six? And so on? I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
