LibreOffice Calc - Date - increase number times...

classic Classic list List threaded Threaded
10 messages Options
zed zed
Reply | Threaded
Open this post in threaded view
|

LibreOffice Calc - Date - increase number times...

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: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: 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: [hidden email]
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

Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: LibreOffice Calc - Date - increase number times...

In reply to this post by zed
Den ons 11 dec. 2019 kl 05:51 skrev zed <[hidden email]>:

> 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: [hidden email]
> 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: [hidden email]
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
zed zed
Reply | Threaded
Open this post in threaded view
|

Re: LibreOffice Calc - Date - increase number times...

In reply to this post by Brian Barker
Brian Barker <[hidden email]> 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: [hidden email]
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

zed zed
Reply | Threaded
Open this post in threaded view
|

Re: LibreOffice Calc - Date - increase number times...

In reply to this post by Johnny Rosenberg
Johnny Rosenberg <[hidden email]> wrote:

> Den ons 11 dec. 2019 kl 05:51 skrev zed <[hidden email]>:
>
> > 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: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: LibreOffice Calc - Date - increase number times...

In reply to this post by zed
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: [hidden email]
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

Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: LibreOffice Calc - Date - increase number times...

In reply to this post by zed
Den tors 12 dec. 2019 kl 05:38 skrev zed <[hidden email]>:

> Brian Barker <[hidden email]> 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: [hidden email]
> 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: [hidden email]
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 Alan Boba
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
wrote:

> Den tors 12 dec. 2019 kl 05:38 skrev zed <[hidden email]>:
>
> > Brian Barker <[hidden email]> 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: [hidden email]
> > 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: [hidden email]
> 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: [hidden email]
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
mikey mikey
Reply | Threaded
Open this post in threaded view
|

Re: LibreOffice Calc - Date - increase number times...

In reply to this post by zed
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 <[hidden email]> 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: [hidden email]
> 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: [hidden email]
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 Alan Boba
Reply | Threaded
Open this post in threaded view
|

Re: LibreOffice Calc - Date - increase number times...

In reply to this post by zed
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 <[hidden email]> 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: [hidden email]
> 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: [hidden email]
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