LibreOffice Calc - Date - increase number times...

users
#1

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

#2

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

#3

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

#4

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

#5

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

#6

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

#7

> > 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?

#8

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

#9

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)

#10

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