[Solved] IF [something] then GOTO cell x:y

[Ozi Ian]

G'day everyone.
I feel I'm a pretty capable user of Calc, but this problem has me stumped. Right now I'm feeling pretty dumb.
I have a "Personal Expenditure" spreadsheet to help us keep track of where all our money goes.

Ideally what I want is the Calc equivalent of 4DOS's "GOSUB" & "RETURN".
[Yeah ... yeah! I'm 70+ and used to program in 4DOS - way before Win95 became popular. ]

In Calc, I want to conditionally jump to a particular cell in the same row. When I've finished there, simply hitting 'Home' would be OK.
ie =IF(C154="V1";GOTO ZZ154;GOTO A155) Enter data in the next three cells (to be manipulated by further cell functions) then 'Home' and 'Down' to enter the next row of data.
Specifically (and ideally), when I enter expenditure on fuel for a vehicle, I can then enter a 'cost code' of "V1".
I want to then be transported to a cell (Column ZZ on the same row) into which I can then enter additional data ie cost/litre and in the next cell, odometer reading, etc, etc.
The next cell on row 154 would be equivalent of 'Return' ie GOTO A155

I've looked through a lot of Mr Google's answers and also in this forum and so far the only 'GOTO' function I've found is linked to error messages or whatever. SIGH!
Maybe it's knowing the right question to get the answer I want? Maybe I've missed something?

I know you clever people will be able to provide a solution, refer me to a link, OR ELSE know how to get the OO Developer Wizards to include GOTO into the "Function" list.
[Wouldn't it be great for OO Calc to be the first spreadsheet EVER to have a "GOTO" function? LOL!]

PLEASE don't mention VBA or other script methods - they scare me to death! I prefer the KISS principle.

As always, any help in achieving my aims will be truly appreciated.
Cheers.

Edit 11-05-2019 Example syntactically corrected.
Edit 16-05-2019 Ref: [OO Calc ... first EVER to have a "GOTO" function?] above. It appears the original "Lotus 123" had an internal 'GOTO' function. Unconfirmed.

[Zizi64]

PLEASE don't mention VBA or other script methods - they scare me to death!

The VBA is a MS Excel related programming language and IDE.
The AOO/LO equivalent is the StarBasic+IDE and the API functions.


Built-in hotkeys:

End : Jump to last non empty column (the cursor will not jump for the actual row)
Ctrl-End : Jump to last non empty cell of the sheet.

Home: Jump to first column (the cursor will not jump for the actual row)
Ctrl-Home : Jump to A1 cell of the sheet.

Ctrl-Up arrow : Jump to first row (the cursor will not jump for the actual column)
Ctrl-Down arrow : Jump to last non empty row (the cursor will not jump for the actual column)


You must all of other jump (control) routines as a macro. And you can assign your macro subroutines to the unused hotkeys.

[Lupp]

=IF(C:154="V1";GOTO ZZ:154;"")

First of all the way you refer to cells is wrong. Syntactically there is nothing like C:154 in Calc.
And basically: Formulae of Calc never "jump" anywhere. They calculate results based on constants and referred values and return them to exactly the cells containing the formulas.

If you want to control and/or support actions of a spreadsheet or its interactions with its users not already supported by the standard UserInterface, you need to write user code, and to call it in an appropriate way depending on your intentions. The user code, written in Basic e.g, allows for the usual sequence control / branching, and there is an API (Application Programming Interface) also providing the means to "jump around" with the current selection in spreadsheets.

Concerning the "jump" case there is one exception: You may use the HYPERLINK() function to create sensitiv cells triggering a jump in case you Ctrl+Click on.

[Ozi Ian]

Thanks for your suggestions.

Yeah. How DID those pesky colons get into the example? Late night brain fade, I guess.

Neither offered solution, however, thrills me too much as both rely on scripts/code (whatever you want to call it and which I specifically said I don't want).
My oldish brain can't handle Basic and APIs. It would be like trying to learn to read & write in a foreign language in 4 weeks!

In any case both rely on additional hot keys (ie not an automatic function depending on the contents of a cell).

Anyway, thanks for your suggestions.
Regards,

[Lupp]

My oldish brain can't handle Basic and APIs...

I wouldn't suggest you do. BTW, I'm 74 and also complain about...now and then.

In any case both rely on additional hot keys...

That's not the case. Using the HYPERLINK() function you get the "sensitive area" in the cell the formula is placed in.
Anyway what I told about formulae not being capable of triggering jumps is simply a fact you need to accept. Something like

Code: Select all

=IF(C154="V1";GOTO ZZ154;GOTO A155)

cannot work. If a "jumping hyperlink" is established with the help of the HYPERLINK() function you need to do the click for ordering the jump, however. You don't need any user code or hotkeys or... on the other hand.

See attached example.

[Ozi Ian]

G'day again Lupp

Re hyperlink: Thanks for a simple example. I think I can do something with that. I had looked at hyperlink but couldn't see how to use it.

Re formulae not being able to do jumps: EXACTLY. So why not try to have the GOTO function built in to the formulae functions by the very clever AOO developers?
[Hope some developers are watching this thread ]

Thanks again,

[Zizi64]

Re formulae not being able to do jumps: EXACTLY. So why not try to have the GOTO function built in to the formulae functions by the very clever AOO developers?
[Hope some developers are watching this thread ]

No, there is not AOO developers in this forum. And there is not too much AOO developer even at the Apache...
If you want new features into a free, open source office suite, then you must use the LibreOffice. There are many volunteers in the developer team. They publishing a new LO version in every three month.

But the feature, what you want, is a nonsense. Imagine, if the functuon GOTO exists: If you apply it more than one times in a spreadsheet, what will decide where to (which cell) the focus must jump after a full recalculation? Which one GOTO will be preferred? After reopening a Calc file the software will jumping endlessly if you apply the GOTO in hundreds of cells.
The obsolete GOTO is an avoided command even in the modern BASIC languages too.

I suggest you: Use the existing features, or write your own macros - if it necessary.

[RoryOF]

in 1968 the great Edgar Dijkstra published a seminal paper: Go To Statement Considered Harmful.
https://homepages.cwi.nl/~storm/teachin ... stra68.pdf

We may not all practice it all the time, but most of us believe it.

Possibly there is another way to tackle your problem.

[Lupp]

Hmmm.

I don't think the OQ is closely related to branching by GOTO (or equivalent) in a program. I consider it being about supporting the handling of some spreadsheets by setting the next cell (or probably cell range) selection as appropriate for the next steps the user has to do under the given conditions.

In fact this is allowed as a side-effect of a user function in AOO and in LibO as well. See attached example.

On the other hand I tend to dissuade from doing it that way. Side-effects of functions should be expected to probably get convoluted. What about a situation where some action is bound to the 'Selection changed' event of a sheet e.g?

[Ozi Ian]

G'day everyone & thanks for input.

I've had a play with Lupp's examples (thanks, Lupp) and my modified version does a pretty good job of getting where I want to go.
[See Attachment]

Only problem now is to find a way of running those lines down 1500 rows. They don't auto correct, presumably as the jump info appears to be "text"?

The manually corrected cells work perfectly. The cell focus goes EXACTLY where it should, each row, every time.

I think some of you are confusing GOTO in programming languages, when I'm looking for a GOTO in Calc. Two very different things.
I'd suggest some of you anti-GOTO people should try to write batch files without GOTO and GOSUB. Two of the most useful functions in 4DOS in my opinion.

BTW the end of my spreadsheet is EH2500 approx, and it takes a long time to get to get to column EH to add data once or twice a month.
The use of a 'Cost Code' ie V1, etc and Lupp's hyperlink makes that SOOOOOOO ... easy.
Also, that cost code does a whole lot of other things in the spreadsheet, so in this case "I DO get to kill two birds with the one stone".

Cheers

[robleyd]

it takes a long time to get to get to column EH

How are you "getting" there? Calc has some useful keyboard shortcuts that may help you navigate quickly around your document.

[Ozi Ian]

Thanks, robleyd.
I use a lot of those shortcuts, but some I don't use I'll have to investigate.

Cheers

Edit 16-05-2019: Ahhhh ... yes ... those ones I don't use relate to functions I rarely, if ever, use.
Thanks, anyway.

[RoryOF]

Turn on Navigator (F5 key, or /View /Navigator); it offers (inter alia) a quick method of navigation to any cell.

[Ozi Ian]

Thanks, RoryOF.
I'm not too sure Navigator does what I am trying to achieve.
I've has a play with Navigator to re-confirm why I don't use it much.
Yes, it can have data inputted to go to a particular cell, but it is far from automatic, as Lupp's hyperlink() samples could potentially be.

I appreciate the input, though.
Cheers

[Ozi Ian]

G'day all, Lupp in particular.

Lupp, In your first examples, I notice you have used a double equal symbol '==' before the 'IF' statement.
IF works just as well with only one '='.
I've noticed this duplication in other forum examples I've tried/used previously.

Historical? Legacy? Makes AOO functions more stable?
I am curious as to why this is done.

BTW1: I have tried to use AOO's 'Fill' function to run the formulae in Lupps examples down the columns. That doesn't work, either.
I really don't want to edit 1500 rows, and besides, the manually edited rows will possibly get well & truly stuffed up with addition or deletion of extra rows, and using 'Sort', all of which can happen on a daily basis.
(Because the row numbers get changed.)

SIGH! We are SO close but also so far away from what I am trying to do. Double sigh!

As originally posted, what I NEED is :

=IF(D35="V1";JUMPTO Z31;JUMPTO A36)

This can then be copied down each row and it will auto correct the references in each row.

Yes, please don't mention again it can't be done when you really mean JUMPTO is not in AAO Calc .... yet.
It MUST be possible. Lupp has proven the basic capability is already within AAO Calc.
Unfortunately I'm not smart enough, or experienced enough, to start playing around with AAO's Calc internal code.
Hence originally asking if 'JUMPTO' (or 'GOTO' ) can be incorporated into AAO Calc by someone way smarter than me.

BTW2: Has anyone tried my modified samples to get the idea of what I am trying to do, and the limitations of the hyperlink() function?
If anyone's interested in the full 'Finance Tracking' spreadsheet I am using (and therefore see WHY I want a 'JUMP' function) I can post a couple of rows with de-personalised info.

Cheers

EDIT: Syntax correction to V1. I don't want the Syntax Police on my tail.

[robleyd]

I have tried to use AOO's 'Fill' function to run the formulae in Lupps examples down the columns

There is no FILL() function; however, you can copy/paste, or use the little square box (handle) at the bottom right of a cell to drag it down/up/across. See https://wiki.openoffice.org/wiki/Docume ... data_entry

[Zizi64]

As originally posted, what I NEED is :

=IF(D35="V1;JUMPTO Z31;JUMPTO A36)

This can then be copied down each row and it will auto correct the references in each row.

Yes, please don't mention again it can't be done when you really mean JUMPTO is not in AAO Calc .... yet.
It MUST be possible.

It is a nonsense to create and use such commands - like the JUMPTO or GOTO - for the Calc formulas.
Fortunately the developers never will create such dangerous cell function. You can do it (with a BASIC macro, based on the API functions) at your own risk. If you will complete it then you will see: this was not a good idea.

[Ozi Ian]

G'day Zizi64.
I chose to not comment on your previous similar allegation for one reason.

We have, in effect, already created such a function. Have you looked at, and played with, my updated version of Lupp's examples.
Do any of my hyperlinks go all over the place when clicked? Do they do ANYTHING other than what I intended? I suggest not.
What's the difference between my IF example and laboriously and annoyingly entering the cell info into Navigator each and every time I wish to add supplemental info to my spreadsheet?
Isn't using 'Navigator' in this way effectively a JUMP function? As I said earlier - the code to "JUMPTO" is already in AAO Calc.
All my IF example is showing is a simple, but not yet existent, "JUMPTO" example of pure simplicity to the user.
Either I haven't understood your examples, or perhaps you could provide further information to verify your allegations of GOTO danger.

G'day again, Roblyd.
I totally agree there is no 'FILL()' function.
I was referring to the "Menu > Edit > Fill > Series > increment by 1" tool. That didn't achieve the desired outcome, either.
I tried your other suggestion and the result is the same.
Neither way will alter cell information in each and every row - much like in my example spreadsheet.

Other thoughts:
If one searches the web for similar requests, (say "GOTO function in spreadsheets) even in the great(?) M$ Excel forum, there are obviously people trying to do EXACTLY what I'm trying to do for exactly the same reasons.
So far I've not seen a reply that isn't an abuse of (If that's to strong a word ... then work-around using) existing Calc functions.

As to "developers never will create such dangerous cell function" So be it if GOTO/JUMPTO is as dangerous as stated. I'm not yet convinced.
In my experience ALL code writers can be very prickly people to deal with if pointing out flaws in their 'perfect' code.
I've also found developers can be very myopic - if they don't think it up, then it can't be a good idea.
And then there are lazy developers ...

SO ...
Is there anyone out there with sufficient insight into the Calc's internal code who can/will help me add a well-controlled JUMPTO function?
Or, like me, is everyone on the Forum just a Calc user - but some more skilled than others?

I'd like to hear your thoughts (as long as they are positive. Hehehe!).


Cheers

[Zizi64]

What's the difference between my IF example and laboriously and annoyingly entering the cell info into Navigator each and every time I wish to add supplemental info to my spreadsheet?

The
"Calling a Function from a Cell"
and the
"Launching a Subroutine by an Event" (by clicking on a hyperlink manually or triggered by a changed cell content)
are basicly different things in a Spreadsheet application.

Yes, you can launch your GOTO() subroutine by a Listener, that will triggered, when the cell content will be changed. It will work.
And that is the reason, why the "GOTO" in a Cell function is dangerous: the cell contents are often recalculated in a spreadsheet document. At Opening, at Saving, and when the cells are changed, and when you force it from the menu or by a hotkey... The recalculation will reactivate the GOTO function too, again and again.
And the controller will jumping "to here" and "to there" at every recalculation for every cell where you have used/applied the "GOTO" function.
For example it will jumping, when you insert/delete a row or column that located before a row/column what contains your GOTO function...

[robleyd]

I tried your other suggestion and the result is the same

I may be misunderstanding here; I think that you want to enter a formula in a cell, then be able to replicate that formula in the following nn number of cells. It isn't clear to me (haven't looked at Lupp's files) whether your need is for all, or part, of the cell references in the formula to be adjusted or not in different locations. However, that can be managed by using the appropriate type of cell reference in the formula - viz. relative, mixed or absolute. See [Tutorial] Absolute, relative and mixed references for more information.

Otherwise, perhaps you could explain how it doesn't "achieve the desired outcome"?

[robleyd]

I was referring to the "Menu > Edit > Fill > Series > increment by 1" tool. That didn't achieve the desired outcome, either.

That tool, I understand, is intended for data, not formulae. For formulae, copy/paste or drag as discussed above are relevant tools.

Edit: As Villeroy says below, it works with formulae and adjusts references. I confirmed this for my own satisfaction by trying it.

Or, like me, is everyone on the Forum just a Calc user - but some more skilled than others?

Essentially, YES. Except substitute OpenOffice for Calc. If you want to request an enhancement, see [Tutorial] Reporting bugs or suggestions. But don't hold your breath

[Villeroy]

That tool, I understand, is intended for data, not formulae. For formulae, copy/paste or drag as discussed above are relevant tools.

IMHO, it is just an equivalent method to do the same thing. When applied to numeric values, the menu command allows you to open a dialog for fine tuning the increment or turn it off all together. When applied to formulas, there is nothing to increment and the cells are just copied which "increments" any relative references just like any other method would do.

[Ozi Ian]

G'day again Zizi64. Thanks for that additional info.
I think I now see what you are saying.
Let me get my head around that info over the weekend.

Cheers,

[Ozi Ian]

G'day all.

Ummmm ... yes, it's been a while.

Zizi64 - yes - I have to agree with you. Using a 'GOTO' in the way I had hoped for simply won't work. Thank you for your patience while I got up to speed.
The obvious mistake I made in comparing 'Hyperlink ()' to GOTO is that Hyperlink requires user action (mouse click) on a cell to initiate a jump.
Humble apologies, Zizi64

Hyperlink() was SOOOO close to being usable except I can't use it without a lot of manual editing, and even then if I add or delete a row it simply fails.

SIGH ...

I'm now looking at adding my data into a single cell in the format a/b/c/d where a=vehicle kilometres, b=number of litres, c=$$s per litre and d=total cost, etc.
Then at the end of the row using something IF(G47="";"-";[extract relevent info from G47]).
In the short term I'll just use 'End' and then tab to appropriate column to add data. Anyway, that's another issue.

I want to thank all people who have helped on this post. It is, in some ways, disappointing to not achieve the desired outcome.
I have, however, always seen a negative outcome as a positive thing - at least you know not to go down that path again, and you then look for another solution.

Once again, thanks, everyone and best regards,
OziIan

Ummmm ... I believe it is up to me to close this topic, but other than selecting the 'Post Icon' green arrow, is there something else I have to do?

EDIT: OK - I found it!

[Villeroy]

Hyperlink() was SOOOO close to being usable except I can't use it without a lot of manual editing, and even then if I add or delete a row it simply fails.

No manual editing required if you really insert and delete rows.

[Zizi64]

Hyperlink() ..., and even then if I add or delete a row it simply fails.

And the Calc formulas have similar behavior too. The formula will be broken, if you delete a row contained a referenced value of the formula.

[Zizi64]

Hyperlink() was SOOOO close to being usable except I can't use it without a lot of manual editing

Not needed type-in all of the letters of a formula when you want to use a Hyperlink function.
Copy the formula, and use dynamic references. Then you need type-in only the Cell reference into an adjacent cell:

Hyperlink.ods

(8.99 KiB) Downloaded 152 times

[Villeroy]

Match_Hyperlink.ods

(39.71 KiB) Downloaded 153 times

[Ozi Ian]

G'day everyone.

If I use the hyperlink() function, it seems I will have to add two extra columns - one for the hyperlink() and one for the cell references.

As mentioned earlier, I had a re-think about another way to achieve the desired outcome.
It involves ENTERING THE DATA IN A STRICT FORMAT into an existing column and along with a code in another existing column, I have been able to parse (right word?) the data into the respective columns for each vehicle at the end of the row.
I can then manipulate that data as I want. I simply press 'End" to go and look at the results; 'Home' to go back to the data entry area.

Yes, it isn't the same method as I was originally looking for, but it works well enough to be usable and is VERY simple for my lady to use.

Typically the cell data looks like "Carlee fuel 003185 57.45 1.499" being Carlee (nickname for our car) 003185 km (the leading zeros are important), 57.45 is the number of litres and 1.499 is the price per litre respectively.
This data is extracted from the RIGHT HAND end of the text, meaning I can input any other information before the km data without affecting the end result.
The code in the "expense code" column ie V21, V31, etc, identifies each vehicle and with an 'IF' function puts the data into the respective vehicle columns.

I'm fairly sure no-one would be interested in how I've achieved this, but if anyone is interested, ask and I can post a section of my spreadsheet.

Once again, thanks to all of you for your time and help. I've learned a little about the very powerful hyperlink() function. Obviously I have a lot more to learn, too.

Best regards,


EDIT: Um ... WOW some pretty fancy stuff in there, Villeroy, way above my poor old brain's ability to soak it all in. ... [Sad & dribbling - LOL]
Zizzy64 - I feel that isn't working for me. I need to be able to run those references down the rows, incrementing by one for each row. I don't seem to be able to do that as text values won't increment the way cell values do in functions.

Time to give it a rest, I think. We've explored (briefly) a "GOTO" solution [epic fail] and hyperlink() - which is becoming way too complicated for my skill level. I've reverted to the KISS method which [so far] is working for me.

[Villeroy]

A1 and B1 are column labels.
Column C is blank in order to separate the data list from the formulas.
Column A has 4-digit text values "1001" until "2033"
Column B has random numbers generated by the rand() function. I used it for random sorting. Click a cell in B and then a sort button on the main toolbar.

The cells showing formulas show the formulas of their left neighbour cells.
Now the task is to look up a certain text entered in E1. E3: =MATCH(E1;$A$2:$A$1034;0) returns the position number where E1 is found in A2:A1034 or error #N/A if the value in E1 can not be found.
For the hyperlink we need a cell address. E5: =ADDRESS(E3+1;2) ADDRESS returns a text value representing a cell address at the given row number and column number. Here I add 1 to the row number in E3 because the first row contains column labels and the MATCH position refers to A2:A1034. I forgot the +1 in my attachment which is why the hyperlink is one row off. Please fix the formula in E5 and add 1 to the matching row number to get the correct address.

H1: =HYPERLINK("#"&E5) refers to the cell address in E5 and prepends a leading #. Hyperlink #$B999 refers to this document, this sheet, position B999.

I found out that the hyperlink breaks when you enter a non-existing value in E1. "9999" in E1 gives error #N/A! in E3 because "9999" does not exist in A2:A1034, all dependent formulas return #N/A as well and the simple formula =HYPERLINK("#"&E5) remains broken even when you enter a valid text in E1. Therefore I add a so called error handler.
H1: =HYPERLINK("#"&IF(ISNA(E3);"E1";E5)) which concatenates the "#" with literal text "E1" if there is error #N/A in E3, so the hyperlink points to the input cell #E1. Otherwise (in case of no error) "#" is concatenated with the text in E5 so the hyperlink points to the cell address in this document, this sheet, cell address in E5.
IF(ISNA(E3) ; "E1" ; E5) means if E3 is #NA, then literal "E3" else whatever in E5.
"#"&IF(ISNA(E3) ; "E1" ; E5) prepends a "#" in front of the address.
wrapping all this in HYPERLINK(...) turns it into a valid hyperlink even in case of error.

It is possible to merge all the single steps in one formula:

Code: Select all

=HYPERLINK("#"&IF(ISNA(MATCH(E1;$A$2:$A$1034;0));"E1";ADDRESS(MATCH(E1;$A$2:$A$1034;0)+1;2)))

and when you insert a new row in A2:A1034, the formula changes to

Code: Select all

=HYPERLINK("#"&IF(ISNA(MATCH(E1;$A$2:$A$1035;0));"E1";ADDRESS(MATCH(E1;$A$2:$A$1035;0)+1;2)))

P.S: E4 is just a little extra demonstrating how to fetch the value at the position looked up by the MATCH function. INDEX(range ; row ; column) returns a value from the given range at the given row and column position.