Hi
How can I select the same cell over multiple sheets in Libreoffice Calc?
And I mean only selecting without entering or changing a value!?
I am using LibreOffice 7.6.4.1 (x86_64) on a Windows 10 64 Pro computer
Thank you
Select to do what?
Calculating with the selection in a formula expression? Use a 3D range like $Sheet1.A1:$Sheet2.A1
thanks for your response
In my calc document I have 12 sheets
The reason I would like that the same cell in each sheet is ‘selected’ or must I say ‘highlighted’, is so that when I scroll over each sheet with ‘Ctrl + Pgup/PgDn’ the cell I would like to ‘edit’ is allready selected. Otherwise I have to go to that cell manually using arrow keys.
I prefer using keyboard over using mouse.
thanks again for your very valid and clarifying question. I should have mention that in my post. Sorry for that.
thx
There may be an easier way to do this.
Click on the tab of the first sheet of your spreadsheet, now hold down the Shift key and click on the tab of the last sheet - all sheets are selected. Select the desired cell on the current, currently visible sheet. In order for this cell to become active on each sheet, you need to make at least some change to it. For example, press F2 to start editing, space to change the cell contents, Alt+Enter to commit the change. Ready! All sheets now have the same cell selected. But unfortunately, the contents of all these cells are corrupted. Revert the contents using Ctrl+Z.
By the way, you can make it easier for yourself to pre-select all sheets using this menu:
Or assign a hotkey to execute the “Select All Sheets” command ( .uno:TableSelectAll)
Thank you for your quick response
Indeed, I use ‘Shift+last sheet’ to select the desired sheets
I don’t need to change a cell of all sheet, just 10 of the 12 sheets. So ‘Select All’ is not an option.
I tried everything you explained.
Selecting desired sheets, clicking one cell and pressing enter, Ctrl+Enter, Alt+Enter, AltGr+Enter, but without changing them … nothing worked
I didn’t try changing the cells because all those cells contains allready formulas or values
So now I did … changing, deleting and so on. Then pressing ‘Alt+Enter’
But even before or after Ctrl+Z, this doesn’t work for me.
and where my ‘frustration’ comes from is that, if I remember correctly, this used to work in earlier versions of LO calc. (or was is OO calc …hm)
so thanks again for your reaction, very much appreciated, but … did not give a solution.
thx
Don’t be afraid, change it boldly - Undo should return everything as it was.
Are you talking about the last ten sheets? From three to twelve?
Try this macro:
Sub setActiveCellOnAllSheets
Dim oCurrentController As Variant
Dim iSheet As Long
Dim arrayOfString As Variant, aParm As Variant
Dim sRow$, sColumn$, sActive$
oCurrentController = ThisComponent.getCurrentController()
iSheet = oCurrentController.getActiveSheet().getRangeAddress().Sheet
arrayOfString() = Split(oCurrentController.getViewData(), ";")
sActive = arrayOfString(3 + iSheet)
If InStr(sActive,"+") > 0 Then
aParm() = Split(sActive, "+") ' This is a relict, I haven`t seen the “+” separator for a long time '
Else
aParm() = Split(sActive, "/")
EndIf
sColumn = aParm(0)
sRow = aParm(1)
For i = 5 To UBound(arrayOfString)
aParm = Split(arrayOfString(i), "/")
aParm(0) = sColumn
aParm(1) = sRow
arrayOfString(i) = Join(aParm, "/")
Next i
oCurrentController.restoreViewData(Join(arrayOfString, ";"))
End Sub
If the code does exactly what you want, you can assign the macro call to a hotkey or place a button to call it on the toolbar
thanks again
I did try to change the cells in different ways, as you’ve said. I can do ‘Ctrl+Z’ to undo everything as original. But with no good result.
In this case I want to change a particalur cell in sheets 2-10 (from 1-12). But that can vary along other spreadsheets, so that, I think, shouldn’t matter.
So then I tried your macro, in which I have very little to no knowledge off.
Recording and Running a macros, so far so good, but writing code and so is still definitly on my ‘to do task’.
But I managed to enter your code. The code works, but in a bit weird way.
In each sheet, I went to cell A1 manually, to get an good overview of the result or your macro. Then I selected the desired sheets, somewhere in the middle of the range. Selected the desired cell, I did that different times, selecting a different sheet from the selects sheet range.
But then the macro selected all cells, until the last sheet. And what is strange, is that one or more cells (this changes) before the selected sheet/cell, were also selected.
But I still could use that, because after having the desired cells in the sheets I want, the rest I do manually with ‘Ctrl+ PgUp/PgDn’ to select the sheet where then the desired cell is select (so I want have to go to that cell manually) and I can do the changes. And ignoring the other sheets.
So this could definitely help me, perhaps assigning a keyboard shortcut for it.
So thanks for that.
But still a pitty a macro is needed to perform, what it seems a simple task, I which there was a better solution, like I think I remember from a long time ago.
Still thank you very much for your time and effort, which is very much appreciated.
Learning on the way
thx
Oh, sorry, I should have anticipated this and provided an explanation.
Since I couldn’t get an idea of the structure of your spreadsheet from previous messages, I assumed that the first two sheets are something summary, and the sheets from the third to the end of the spreadsheet will be edited. It is on this assumption that the script was compiled and this is exactly how it works: it looks at which cell is active on the current sheet (on any sheet) and makes the same cell active on all sheets from the third to the end of the spreadsheet.
No problem, I managed to get it done, and the macro works just as you explained.
(beside that sometimes weird behavior when there are some sheets selected, the macro also selects that cell in one or more sheets before the first selected sheet. That is, in my case and spreadsheet ofc.
But that is not a problem, because I focus then on the individual sheet I want to edit with ‘Ctrl + PgUp/PdDn’, and ignore the others.
In this particular case, the sheets where I want to select the cell, are somewhere in the middle of the spreadsheet. But like I said, I have many spreadsheet, and different actions on different sheets to be made.
So thanks again and I will mention your answer as the solution. For the moment it is sufficient and I will look further into it. All very interesting stuff.
So little I know, so much to learn.
Just a pitty that there in not an easier and more logic wayto do this (like I think I remember from a while ago). And this only on the selected sheet.
But once again thank you very much for your time and effort.
(And btw, this is my first experience and interaction on this site and allready had a very very good experience. Nice.)
Thank you