This is a pretty daunting task. To solve it, you will need to use the Selection Changed event, and using this event with inaccurate coding can lead to a program loop, which in turn will cause crash of Calc.
A rough, draft, unreliable solution might look like this: when an event occurs, check whether the number of the previous sheet and the current sheet matches. If the sheet has changed, try to find in the first column the value from the current line of the previous sheet. If successful, select a cell on the current sheet in the found row and in the previous column, select it. Otherwise, select cell A1.
Global variables can be used to store previous values for Sheet, Row and Column.
Another global variable _JumpProcess should help prevent the program from looping.
REM ***** BASIC *****
Option Explicit
Global _JumpPrevSheet As Integer
Global _JumpPrevColumn As Integer
Global _JumpPrevRow As Long
Global _JumpProcess As Boolean
Sub OnSheetChange(Optional oEvent As Variant)
Dim nCurrentSheet As Integer
Dim oPrevSheet As Variant
Dim oPrevValue As Variant
Dim oSheet As Variant
Dim oTargetCell As Variant
Dim oController As Variant
If _JumpProcess Then Exit Sub
_JumpProcess = True
oSheet = oEvent.getSpreadsheet() ' Current sheet
nCurrentSheet = oEvent.getRangeAddress().Sheet
If _JumpPrevSheet <> nCurrentSheet Then ' Sheet was changed
oPrevSheet = ThisComponent.getSheets().getByIndex(_JumpPrevSheet)
oPrevValue = oPrevSheet.getCellByPosition(0, _JumpPrevRow).getValue()
oTargetCell = getCellWithMatch(oSheet, oPrevValue, _JumpPrevColumn)
oController = ThisComponent.getCurrentController()
oController.select(oTargetCell)
oController.select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
EndIf
_JumpPrevSheet = nCurrentSheet
_JumpPrevRow = oEvent.getRangeAddress().StartRow
_JumpPrevColumn = oEvent.getRangeAddress().StartColumn
_JumpProcess = False
NoAction:
On Error GoTo 0
End Sub
Function getCellWithMatch(oSheet As Variant, vMatchValue As Variant, nColumn As Integer) As Variant
Dim oCursor As Variant
Dim nRow As Variant
Dim sFA As Variant
oCursor = oSheet.CreateCursor()
oCursor.gotoEndOfUsedArea(True)
oCursor = oSheet.getCellRangeByPosition(0, 0, 0, oCursor.getRangeAddress().EndRow)
sFA = CreateUnoService("com.sun.star.sheet.FunctionAccess")
nRow = sFA.callFunction("MATCH", Array(vMatchValue, oCursor, 0)) - 1
If nRow < 0 Then
getCellWithMatch = oSheet.getCellByPosition(0, 0)
Else
getCellWithMatch = oSheet.getCellByPosition(nColumn, nRow)
EndIf
End Function
Assign the OnSheetChange macro as an event handler for each of the sheets in your spreadsheet.
Example of using the code - SmartJumpBtwnSheets.ods
