[Solved] Get date for event in next column

[okjhum]

How to retrieve a date from column A depending on an event on a corresponding row in another column? Couldn't find this in the forum.

I have a large file with many sheets, one for each month since a number of years

In each sheet I have that month's dates in the first column (A) and lots of other columns for various types of events. (Actually a record of the quickly migrating symptoms of my disease; too difficult to remember what happened where and when, if at all.)

The events are recorded on the date row coded as a number from 0.1 to 5. Usually no entry at all if nothing happened.

Below that I manually enter the date of the most recent event and carry it forward to the following month(s). I was wondering if that could be automated, but I don't seem to manage VLOOKUP or any other algorithm that does this:

IF there are any events at all this month;
THEN enter the date of the last of them as shown in column A;
ELSE copy from previous month

Attaching a sample spreadsheet. The formula cell is B35 in this example.
Thank you in advance.
/Olle.

[FJCC]

I put a proposed solution in B35 of the June sheet with a helper function in B40. Note that B40 is an array formula with curly braces around it. The formula is entered without the braces but you finish it by typing Ctrl + Shift + Enter instead of a simple Enter.

The logic of the helper function is:
ISNUMBER() returns TRUE or FALSE where TRUE = 1 and FALSE = 0
ROW() returns the row number of the cell
ISNUMBER() * ROW() returns either 0, when ISNUMBER() is FALSE, or the row number, when ISNUMBER() is TRUE.
MAX() returns the largest row number from ISNUMBER() * ROW()
INDEX() goes to the position in A1:A32 of the maximum row number from the previous results.

You would probably be far better off having all your data on one sheet and only entering days that have an event. Does something prevent you from doing that?

[okjhum]

Thank you, FJCC! It looks promising. Now I'm struggling to understand ARRAY formulae and why I can't edit the range in your formula although I believe I'm following OpenOffice's help text exactly. I wanted to change the range to start from row 2 (A2 and B2, respectively) or else i'm getting the column A header as a reply when there is no event at all in column B. That's strange, because the header text cell is FALSE in ISNUMBER. And why the coulmn A header instad of B?
But when I enter the same array formula from scratch but with range from row 2 instead of row 1 {=INDEX(A2:A32;MAX(ISNUMBER(B2:B32*ROW(B2:B32))))} in other help cells as well as in B40, they return very crazy results. Why is row 1 so sacred?

[FJCC]

There is nothing special about starting the formula in row 1. What other help cells do you need? Can you upload the document that is giving you strange results?

[Alex1]

=INDEX(A1:A32;MAX(ISNUMBER(B1:B32)*ROW(B1:B32)))
The parentheses are wrong in your formula:
=INDEX(A2:A32;MAX(ISNUMBER(B2:B32*ROW(B2:B32))))
Even when you correct that, FJCC's formula uses the wrong offset when it's modified to start another row than row 1.
This formula gives the required result however:
=INDEX(A1:A32;MAX(ISNUMBER(B2:B32)*ROW(B2:B32)))

[okjhum]

...
This formula gives the required result however:
=INDEX(A1:A32;MAX(ISNUMBER(B2:B32)*ROW(B2:B32)))

Thank you Alex1. It works indeed.
Now, how do I get it to display the result (value) of the previous month's same formula, as long as there are no events in the current month? I.e., when ISNUMBER is false in the whole array?
I tried {=IF(COUNTIF(B2:B32)=0;'2024-05'.B35; INDEX(A1:A32;MAX(ISNUMBER(B2:B32)*ROW(B2:B32))))}
but it returned the text of cell A1 plus inserted all the days of the whole months in the rows below, replacing everything that already was in those cells.
Also, {=IF(ISNUMBER(B2:B32); INDEX(A1:A32;MAX(ISNUMBER(B2:B32)*ROW(B2:B32))));'2024-05'.B35)} yields Error.

[Alex1]

I would advise to put all data on a single sheet instead of using a sheet for each month.
Then a pivot table can be used to show the number of events and the accumulated number per month.

[okjhum]

Oh horror! I wish I would have done that from the beginning. I never understood the pivot table thing, though I tried.
I have collected every month since august 2013. I wanted to see if the occurrencies and severities of my migrating RA symptoms were related to my physical activities (practising Aikido) or if I would have to quit it. Even at times of few or no symptoms I kept logging my aikido practice.
For each month I have, at present, symptoms in columns B to AS. Then a few columns for medications and for activities. And finally column BE for annotations.
Under each column I have cells with various statistics, which are then carried over to some tabs with various summaries.

I will need time to ponder upon your suggestion. Maybe start anew?

Thank you again!
/Olle K.