How can I jump to a referenced cell?

English
, , , ,
#1

Hi,
let’s say I’m at Sheet1 cell A1. This cell takes the value of Sheet2, cell B2. Is it possible for me to go to Sheet 1, cell A1 and ‘jump’ to Sheet2, cell B2 automatically? If not, is there anyway that I could create a function that would allow me to do that? I know in Excel, I could write a VBA program to do that (although I’d rather not).

Thanks!
NN

#12

A macro solution would be straight forward, if the required API function would work as expected.
https://bugs.documentfoundation.org/show_bug.cgi?id=159973

#14

Found a not so straight forward solution:
gotoPrecedents.ods (26.2 KB)

It is far from perfect because I do not fully understand how to parse formula tokens correctly, i.e. named references or references to database-ranges are disregarded (not listed)

You may import the library to MyMacros, so it can be used with all spreadsheet documents:
Tools>Macros>Organize…
button [Organizer…]
tab “Libraries”
Location “My Macros”
Button [Import…]
Point to my file and select library “GoToPrecedents”.

#15

Nor do I.
But as chance would have it:
For a completely different reason, I tried to get a little more familiar with the FormulaParser service the day before yesterday. From parts of the corresponding “research code”, I have now quickly and somewhat sloppily created a function that generates a list of all references that occur in the formula of a cell.

The function and an example how to use it ist contained in the attachment.
FormulaParser_playful.ods (13.1 KB)

2 Likes
#17

Did somebody try the workarund I attached above?
I would be interested in comments.

#18

Yep. Letting the formula parser do the work is very clever. I had no idea, how to do something like that and wrote my own formula token parser.
One thing: ((qO=0) OR (qO)) is obsolete.

P.S. Your code lists external references as well. Mine doesn’t because I forgot about those, therefore my parser does not handle them.
In case of array formulas =A1:B5, your function returns #NAME? for any cell but the first one, whereas my parser returns a reference to the first cell instead of the #NAME? error.

1 Like
#19
  1. Copy your module into my lib.
  2. Remove Option Explicit
  3. Set cell = pCell
  4. In my code:
	REM fillListArray tks, a()
	s = listReferences(ac)
	a() = split(s, "/")

Now we need some change in my dialog’s ItemListener in order to select the referenced ranges. This will fail where named references are relative, which is why I resolved the names recursively.

#20

There was a typo. The expression should read ((qO=0) OR (qO=4)).
I simply didn’t know if there are additional OpCode values for which (qT="Object") would come out true.
(Assumed 0 for direct references, 4 for named ones as far as I understood. I didn’t find a doument telling me clearly.)

#21

This is why I used the type names of the structs.

Function getTypeNameOfStruct(v)
	getTypeNameOfStruct=Split(v.dbg_properties, """")(1)
End Function

… together with OpCode 19 which seems to indicate a database range.

#22

I have to admit that I didn’t study your solution (.oxt). I can’t simply accept the defaults of exchange “services” as they are, and I lack the patience to go through everything they offer in a bypass.
I tried to recognize the needed references the way I did because OpCodes 0 and 4 also are used for direct (literal) values. DB ranges and DB...() functions I never used myself.
(The original reason for what I studied the formula parser recently wasn’t in any way related to precedents.)

#23

The .oxt has been created by the Basic organizer (tab “Libraries”, button “Export”). It contains a single Basic library. You can extract the library folder from the oxt and import the library without extension manager.

#24

OpCode

FormulaMapGroupSpecialOffset

Great job.
Particularly liked the idea of ​​bringing in a formula expert like FormulaParser instead of directly analyzing tokens.

#25

2 similar lists but not the same, adding more confusion.

#26

The first link is the full list.
I gave the second link (maybe in vain) because it contains explanations for some special codes (don’t look at the number, but at the name: PUSH, WHITESPACE, …).

1 Like
#2

Use the following hotkeys to jump to the reference cell (from cell $Sheet1.A1 to cell $Sheet1.B2) :

CTRL + [

note : the same hotkey works in excel.

1 Like
#3

I believe you actually mean:

(from cell $Sheet1.A1 to cell $Sheet2.B2)

 :)
#4

What if a formula contains multiple cell references and I want to jump to one of them by selecting it in the formula? I tested this with the Ctrl+[ shortcut but it only jumps to the first referenced cell.

#5

There are very few operations you can apply to selected parts of cell contents.
AFAIK only setting character attributes (not persitent when editing a formula) or copying to the system-clipboard will work.
If you are fond of developing user code, you can have a routine extracting the references from the formula or getting them from the respective tokens and giving you the choice which one to use for the jump.
Too much effort for little use, IMO.
If you have copied a reference when editing a formula, and pasted it elsewhere, it’s simple to use the HYPERLINK() function in the usual way to get a link triggering the jump.
disask39770_4JumpToReferencedRanges.ods (14.5 KB)

#6

Thanks. I won’t use that though because I can’t afford to add more content in my sheets. And a macro that would pop up a dialog in which you have to chose which reference to select is far from what I was hoping for.

It would be such a useful feature if you could, say Ctrl+click on a cell reference while editing a formula and it would just shift the view (without leaving the formula) to center it on the referenced cell or range. On very large sheets, I still know no alternative to reading the reference and manually going there using the sliders, which is quite distracting when you’re doing math in your head to understand a formula. I’m surprised that in all these decades it hasn’t been a highly demanded feature.

#7

If you don’t want to leave the edit mode of the formula. you surely can’t “jump” to a referenced cell or range. And you can never “jump” to more than one range at a time.

But just while editing a formula, references are emphasised by colored borders anyway if you didn’t disable the option
>Tools>Options>LibrOffice Calc>View>>Display:Show references in colour.
Most what you probably need to do, is showing the respective sheet by clicking its tab and probably scrolling it (doesn’t leave edit mode).
(Cuboids once more are, an exception - but you will rarely be tempted to use them.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.