Focus in other sheet

Libreoffice Calc version is Ubuntu 20.10

Sheet1 have these values :


Sheet2 have these values

If I am on third row in these sheet1 whose value is 30 and if I switch on sheet2 my focus should be on row 4 whose value is 30 and if I move to 5th row in sheet2 then my focus should be on first row in sheet1 whose value is 10.

So, my question is how do I traverse or set the focus on a row based on a cell value. If there is no matching value finds in either sheet then focus should be on first row first column.

How do I achieve this.

Any update please.

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()""))
	_JumpPrevSheet = nCurrentSheet
	_JumpPrevRow = oEvent.getRangeAddress().StartRow
	_JumpPrevColumn = oEvent.getRangeAddress().StartColumn
	_JumpProcess = False
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 = oSheet.getCellRangeByPosition(0, 0, 0, oCursor.getRangeAddress().EndRow)
	sFA = CreateUnoService("")
	nRow = sFA.callFunction("MATCH", Array(vMatchValue, oCursor, 0)) - 1
	If nRow < 0 Then
		getCellWithMatch = oSheet.getCellByPosition(0, 0)
		getCellWithMatch = oSheet.getCellByPosition(nColumn, nRow)
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

Thank you for such a nice code.

No @GirishSharma, this is not “nice code”, this is only an approximate solution to your problem. This does not work well if the active sheet is not the first one after opening the spreadsheet. It will give an error if you click on the inserted image. To avoid mistakes, you will need to write more and more code. It makes sense to consider alternative ways. For example, replace the values in the first columns with hyperlinks to the corresponding places in the adjacent sheet.

Thanks for your reply. Actually, I am foxpro user in which we have a set relation command, by which record pointer is auto moves in child table as we moves in parent table based on index key. So, I supposed here in Calc it is also possible. I very novice person in Calc, so I can’t write more code. I suppose in next release, developer may consider this as a feature request.