Update Calc date cells from sheet to sheet

fast-eddie · March 8, 2021, 4:15pm

I have a worksheet labelled JAN, in which; Cell B2 is set to date value and contains the text JANUARY.
When i copy this sheet, to make a FEB sheet; i want Cell B2 to automatically change to show FEBRUARY.
Subsequently, update each time i copy the previous sheet; to make the next month.
Is this possible?
i have the cell set as per the ata

ched screen shot.

anon73440385 · March 8, 2021, 5:00pm

set to date value and contains the text JANUARY

This is somehow contradictory. Either it contains a date value (an then it is an integer number) or it contains a text and then it is not a date value (don’t confuse formatting, i.e what you see, with the real content of a cell). Could you please clarify the ambiguity?

Lupp · March 8, 2021, 5:19pm

Calc won’t worry about a text probably being meant to mean something calendaric.
The one-sheet-per-month design should be considered doubtable anyway, but if you insist on it, and your sheets are named “January” and so on, you would need a formula returning the sheetname to cell B2 of each sheet.
Since there is no standard function doing so, you either need to resort to complicated tricks, or to write a user function for the purpose.

fast-eddie · March 9, 2021, 8:42pm

thanks, the below formula has worked for my needs.

Lupp · March 8, 2021, 5:23pm

User code as mentioned in my comment on the question might look this way:

Option VBAsupport 1

Function sheetNameByReference(pRef)
REM VBAsupport required.
sheetNameByReference = pRef.CellRange.Spreadsheet.Name
End Function

Function sheetNameByNumber(pZ As Long, Optional pDoc)
REM This function does not require VBAsupport.
If IsMissing(pDoc) Then pDoc = ThisComponent
sheetNameByNumber = pDoc.Sheets.ElementNames(pZ - 1)
End Function

It is raw, withot any error-catching.

fast-eddie · March 9, 2021, 8:42pm

Thanks for the info.

anon73440385 · March 8, 2021, 5:14pm

Hello,

provided you are starting with sheet one being Sheet JAN and monthly sheets continuously appear as following sheets (i.e sheet 2 = FEB through sheet 12 = DEC) - put the following formula into B2:

=UPPER(TEXT(DATE(2021;SHEET();1);"MMMM"))

Personally I’d prefer (and recommend) to not have uppercase , no text and

using formula =DATE(2021;SHEET();1 in cell B2
formatting cell B2 using Format Code MMMM

This way you’d have a real Calc date in cell B2, which could be used for further calculations.

Hope that helps

fast-eddie · March 9, 2021, 8:42pm

Th is works perfect thank you all i had to do was change the 1 for a 0 as i have an initial sheet.