This solution uses a macro that will fire when any cell in the sheet changes.
First of all, create a custom cell style. Click F11 to open the window Styles.
Right click on the Default style and select New. Give the style a name that begins with a keyword. I used the _related prefix as an example.
You can also assign a background color to this style to help you avoid mistakes when laying out linked cells. When the sheet is ready, you can reset this parameter to None.
Create as many styles as you are going to create links, just add the following number after the prefix.
Now add a macro with the following code to the Standard module of the current spreadsheet:
Option Explicit
Sub onCellValueChanged(oEvent As Variant)
Const START_STYLE_NAME = "_related"
Dim oSpreadsheet As Variant
Dim oSearchDescriptor As Variant
Dim oFound As Variant
Dim oRange As Variant
Dim oCell As Variant
Dim i As Long, j As Long, k As Long
If oEvent.supportsService("com.sun.star.sheet.SheetCell") Then
If Left(oEvent.CellStyle, Len(START_STYLE_NAME)) = START_STYLE_NAME Then
oSpreadsheet =oEvent.getSpreadsheet()
oSearchDescriptor = oSpreadsheet.createSearchDescriptor()
oSearchDescriptor.SearchStyles = True
oSearchDescriptor.setSearchString(oEvent.CellStyle)
oFound = oSpreadsheet.findAll(oSearchDescriptor)
For i = 0 to oFound.getCount()-1
oRange = oFound.getByIndex(i)
If oRange.supportsService("com.sun.star.sheet.SheetCell") Then
oRange.setString(oEvent.getString())
Else
For j = 0 To oRange.getRows().getCount()-1
For k = 0 To oRange.getColumns().getCount()-1
oCell = oRange.getCellByPosition(k,j)
oCell.setString(oEvent.getString())
Next k
Next j
EndIf
Next i
EndIf
EndIf
End Sub
Now right click on the sheet tab and specify that this subroutine should run for the Content changed event.
Demo example - multycell_editing.ods (11.2 KB)
Another solution (without a macro) can be implemented using the CheckBox controls located in the linked cells. It is enough to specify the same Linked Cell in the properties of different controls (for example, A1 for your example).
In this case, cell Z1 will indeed contain the formula that you indicated in the question. Clicking on these checkboxes will change the value in one cell, but this value will be displayed wherever there is such a formula.
