Good morning
I have a Calc file with about 30 worksheets.
Each worksheet is renamed every time I insert data.
In cells i4, i8, i13, i18, i20, i28, i30 of each sheet I have a date field with this format “NNNNDD MMMM YYYY”.
I would like to insert a “date field” in all the sheets in the cells listed above with the link to the same cells.
Please, can anyone help me?
Thank you
Let’s say your master cell on each sheet would be cell A1.
Then
-
write into cells i4, i8, i13, i18, i20, i28, i30 of first sheet the formula
=$A$1 -
select cellrange i4:i30, hit ctrl+c
-
Now select 1st sheet in the bottom sheet-tabs, hold shift, and select the last sheet. All sheets are selected now:
-
click with the cursor on cell i4 of first sheet
-
hit ctrl+v
-
in all cells i4, i8, i13, i18, i20, i28, i30 on all sheets you should find the formula
=$A$1
done!
See sample file
linked_cells.ods (12.2 KB)
linked_cells.ods (12.3 KB)
I badly explained myself
I have attached your fgaile with the corrections.
I can’t attach the “Date Field” to the respective cells
i4, i8, i13, i18, i20, i28, i30
I found this answer in this forum
The proposed solution is the creation of a macro.
Sub DateFieldInCellA1
myDoc = ThisComponent
oForm = myDoc.DrawPages("Sheet1").Forms.GetByName("Form") ' Select Form in Sheet
oCtrl = oForm.GetByName("DateField1") ' Select Date Field
mySheet = myDoc.sheets("Sheet1") ' Select Sheet for Output
mycell = mysheet.getCellByPosition(0,0) 'Select Cell A1 for Output
mycell.value = CDate(oCtrl.Text) 'Output the Value for Date
End Sub
{Formatted as ‘Preformatted text’ by @Lupp for better readability.}
…
This macro works even if I change the name of the sheet (for example alpha1)
But this function does not work in other sheets
Look, why do you need so many controls to enter the date? If you are going to use a macro anyway, then why not use a very old solution: just designate the cells in which you need to enter dates with a special style, and the macro will create a control for this one cell at the time of editing. One control for current cell.
calendar.ods (15.5 KB)
It only works at all (but only in the first one of all the sheets) because the container property myDoc.Sheets expects an integer (Long) number as the index, and automatic conversion in Basic interprets any string in such a position as an equivalent of 0 (zero). Therefore you get the first (index=0) sheet of the collection independent of the string you actually place there.
Extended implementation of automatic conversion comes with disadvantages!
You need to be cautious.
Yoiu need to either give the correct 0-based index of the sheet in you statement, or to use mySheet = myDoc.Sheets.getByName(actualSheetName) with the correct name.
I would like to insert the date by selecting the date from a calendar with the mouse instead of writing the date inside the cell
The “Date Field” solution allows me to do this.
Then connected to the date, for example February 26, 2024 I have this formula in cell F6
=LINK(“https://www.google.com/calendar/event?action=TEMPLATE&text="&F3&"&location="&B17&"&dates="&TEXT(J4;“yyyymmddThhmmss”)&"/"&TEXT(J5;" yyyymmddThhmmss”)&"&details="&H2;“ASSESSMENT CALENDAR”)
Today, in cell I4 I write the date
In cell g4 I write the time with this format 16.00.00 (hh.mm.ss)
In cell g5 =g4+k1
k1=00:30:00
So the result of g5 will be 16.30.00
In cell j1 = TEXT(I4;“yyyymmdd”)
j2=TEXT(G4,“hhmmss”)
j3=TEXT(G5,“hhmmss”)
j4=J1&“T”&J2
j5=J1&“T”&J3
B17= variable text
So with the mouse, I would like to select the date (it would be the maximum from a “Time Field”), then select the date field and finally select cell F6 which contains the link
I attach an example
I have filled in only the cells that are of interest.
Sheet columns A to U are all filled in
There are 26 sheets in the file
The name of the sheet is changed every time I insert a new name. The number of sheets may also vary
calendar1.ods (17.6 KB)
Your date fields are only pictures you have insert in a cell. The value of that cell is NULL (zero), have a look onto my file:
linked_cells_WiTHOUT CALCULABLe CONTENTs.ods (11.4 KB)