[Dropped] Move selection to another cell when a cell changes

[imadumby]

Edit: Marked [Dropped] since OP says: "This thread has become too long and confusing" and opened a new topic.

Suppose you have this in cell A1:

=B1

Now you type something in B1, like for example, a "1" and A1 becomes 1.

When that happens I need the selection to go down to cell A26 (I mean the next selected cell would be A26).

I tried doing this by rightclicking the sheet tab at the bottom of the sheet and use "sheet events" but couldn't figure out how to do it there. I need the selection to work only when cell A1 (or whatever cell I'm using) changes, and not when any other other cell changes (hopefully without needing a macro).

Anyone know how to do this?

Thanks again for your help!

[RoryOF]

What is the range of possible values to be allowed in B1, and what reaction do each of these require on your sheet?

[imadumby]

Range of possible values in B1is:
Zero(0) if I DONT want the selection to move or
One (1) if I DO want the selection to move.

For what I'm doing, if "1" ever does go into B1 (and then, by extension, into A1), it can only possibly happen 1 time and that's all.

[imadumby]

I don't appear to be getting any action on this.

Is what I'm asking impossible to do?

[robleyd]

For what I'm doing,

This sounds like it might be an XY problem. Can you describe what you are really trying to achieve? There may be alternatives that could be suggested.

[imadumby]

I dont know if this will help or not but its like this.

I have a bunch of switches hooked to my computer/spreadsheet, which aren't activated by human hands. Now if a certain rare but possible combination of switches happened to be switched, the selection must automatically move (without a person doing the moving) to a different area of the spreadsheet (Cell A26 in this case) to be able to continue to receive inputs from the switches.

What do you think?

[Lupp]

...I have a bunch of switches hooked to my computer/spreadsheet, ...

Actually I can hardly imagine somebody understanding this as a useful statement.
"Bunch" may pass, though not clearly being related to spreadsheets, but "switches" and "hooked" clearly aren't terms well known to spreadsheet users. (And: This is an international forum in English language.
If the switches are "cells showing a Boolen value" call them this, if they are form controls of the "Check Box" type, call them that.
Be more expressive abut how the "rare combination" is detected and made manifest.

Anyway, don't expect a ready-made tool for the evalaution and any action depending on the result. Most likely you will need to develop a bit of user code and to assign the main routine of it to the sheet event I would like to name "onFormulasRecalculated" though I have to consider that it looks a bit different in the UI.

And once again: What you actually want/need to achieve may be something different ...

For a raw example of what I think you might want, see the attachment.
It can only do as expected if you permit the execution of the macro (after having checked it for the absence of malign code.)

aoo105367selectConditionally.ods

(19.56 KiB) Downloaded 144 times

(No need to use.)[/strike]

Editing:
Sorry (kind of)!
If my considerations concerning what to achieve are correct, it's much simpler.
Changing the selection isn't a forbidden side-ecfect of functions - as I had assumed.
Therefore an ordinary user function can do the calculation of the "complex" condition and the "jump" to the new selection in one go. No sheet event needed to listen to! I provide a new example demonstrating how to do it this way.

aoo105367selectConditionallyByFunction.ods

(20.45 KiB) Downloaded 168 times

[imadumby]

Sorry, didn't mean to cause confusion.

Robleyd asked what I was really trying to achieve and I thought that's what I was trying to describe. What I'm really trying to achieve is to make a certain result (like "1") go into Cell A1 and when it does, have the spreadsheet make the next selection (or, go to,) Cell A26 (which is a cell that already is blank and will, once used, not be used again because the "action" (the move) cannot be triggered again. Thats because the the combination that created the "1" cannot happen again).

For more detail, what I'm trying to do pretty much could be explained like this: if a certain keystroke (like a "1") is made in a certain cell, then the spreadsheet makes the move. Its as simple as that.

Your example looks good but A1 has a function I've never seen before, and it doesn't seem to work for me. When I copy and paste it to my spreadsheet A1 says "#MACRO?" instead of "#VALUE!" like yours says. Not sure why, but when I go to see your example I get this message:

"This document was created by a newer version of Open Office. It may contain features not supported by your current version. Click “Update Now...” to run online update and get the latest version pf Open Office."

I'm using version 4.1.7. Do you think that's the problem or maybe something else?

[robleyd]

This document was created by a newer version of Open Office

The esteemed Lupp uses a recent version of LibreOffice which stores documents using the Open Document Format version 1.3, where Apache OpenOffice currently uses 1.2, leading to the message you received.

I think Lupp was also asking what you meant by "switches hooked to my computer". I'm guessing that you have external physical switches of some sort and are reading the settings of those switches via USB/ serial port/ some other interface, and a macro of some sort??

[Lupp]

I also tested the attached example with AOO 4.1.7, and it worked as expected when I ignored the alert.
An update to AOO wouldn' change anything anyway, because AOO didn't upgrade to ODF 1.3 yet (afaik). Backward compatibility is good enough.
Concerning the "switches": I also considered any hardware connected via any hardware-interface. However, as soon as something was successfully read in from a port it should be usable inside the sheet as if typed in via the keyboard.

[imadumby]

Concerning the "switches": I also considered any hardware connected via any hardware-interface. However, as soon as something was successfully read in from a port it should be usable inside the sheet as if typed in via the keyboard.

Thats exactly what they are, switches (or buttons, if you prefer) that, when pressed work exactly like keys in a keyboard and enter a number or letter into a cell, whatever it has been programmed to give.

[imadumby]

OK, I got rid of the problem that was giving me "#MACRO?" instead of "#VALUE!" like what your example has in cell a1 by saving it as an ods.file (I saw this here: viewtopic.php?f=9&t=21974 ).

Now I have copied and pasted your version to my spreadsheet (version 4.1.7) and it looks all the same, except that for whatever reason it won't work.

To match what I'm looking for I then changed the function to this: =XONCONDITIONJUMPTO(AND(B1=1;ISBLANK(A26));J1;K1)
and made Cell J1 this: A26:A27, then I entered 1 into cell B1 and nothing happened.

Wonder whats going on?

[Lupp]

Well, assume you entered your 1 into B1, or there was calculated a formula returning 1.
The automatism (based on the formula in A1) selects (say) A26.
Something may be entered there subsequently, I suppose.
Next time something is calculated in the sheet and A1 is "dirty" for one or another reason, it will recalculate, and trigger the selection of A26 again.
This should not be what you want.
Therefore I added the condition (adapted to the A26-case) ISBLANK(A26) causing the formula to trigger the jump only if the target cell is blank (really empty; not even containing a formula returning the empty string).
But if you enter/confirm the 1 in B1 next time, nothing will happen if meanwhile something was entered into A26.
This should again not be what you want.
"What?", "Why?" first.
Then "How?"

What did I miss in your statements?

[imadumby]

"Well, assume you entered your 1 into B1, or there was calculated a formula returning 1.
The automatism (based on the formula in A1) selects (say) A26.
Something may be entered there subsequently, I suppose."

>>>YES. The next input from the "switches" (buttons or keystrokes, whatever) is entered at this point into cell A26. Suppose it was a "5". A26 would then have a 5 in it.

"Next time something is calculated in the sheet and A1 is "dirty" for one or another reason, it will recalculate, and trigger the selection of A26 again. This should not be what you want."

>>>That's right. Dont want that. But the thing is, in this spreadsheet once the value of cell B1 (and by extension, the value of cell A1) reaches "1" it cannot change. The value of Cell B1 is the result of a combination of inputs that have already happened and cannot change. If, when you say that cell A1 could somehow be "dirty" I'm not sure what you mean. Cell A1 starts out with that formula in it and could possibly later trigger that function, but once it does it can't do it again because the combinations that could trigger it would then be in the past.

"Therefore I added the condition (adapted to the A26-case) ISBLANK(A26) causing the formula to trigger the jump only if the target cell is blank (really empty; not even containing a formula returning the empty string)."

>>>Ok, I understand that. But at this point (before cell b1 turns into 1) A26 would be blank without any formula in it. See, normally in this spreadsheet A26 would be the 26th input. But if the combination that forces the jump to happen actually occurs it would happen no later than the 10th input. If it hasn't occured by the 10th input, it wont happen at all.

"But if you enter/confirm the 1 in B1 next time, nothing will happen if meanwhile something was entered into A26.
This should again not be what you want."

>>>No don't want that but I'm not worried about it. Again, if the 10th input doesn't make the jump occur, nothing will. And if it does, there is nothing else on the spreadsheet later that will make any other jump occur.

"What?", "Why?" first.
Then "How?"

"What did I miss in your statements?"

>>>I don't think you've missed anything. I just think you may be overthinking it.
Think of it this way:
If a certain combination happens early in the spreadsheet, then that jump has to be made, and then the jump is in the past and can't happen again.
If it doesn't happen early, it's past the stage where it might possibly happen and it just wont happen at all.
Or this way:
On your spreadsheet put up a column of 20 random numbers.
Now look at the first 10.
If they are all EVEN, think: AHA! The combination hit! The first 10 are even! The selection must now move to A26!
But if 19 of them are even but the first one is ODD think: There is NO jump to A26 because the first 10 numbers aren't even!

**********
Regardless of what happens, somehow I am back at the stage of having this error message in Cell A1: #MACRO? instead of this: "#VALUE!". If I take the "Kangaroo" (which is text) out of the equation I then have the error message #NAME?

[imadumby]

OK.

I have posted a spreadsheet which shows an example of what I'm doing (but using a fictional-and unlikely-situation).
It centers on Cells F8 and F9. You'll see that the answers to these 2 questions are the only things in my situation that can trigger cell A1, and everything else is totally irrelevant and cannot trigger A1, so to add extra variables really only complicates matters.

At the bottom I propose changes to adapt Lupp's work to my situation and I wrote notes on why I did it. Then I list my problems (like, for example, none of it works for me) and ask my questions.

I'm amazed Lupp came up with this function, now if only I-actually I mean you all-can only make it work for me. Hope I can get some help and the forum can end up with another cool "SOLVED!"

[robleyd]

You may have missed that the esteemed Lupp has embedded a macro in his sample sheet; said macro provides the user defined function XonConditionJumpTo. As you hadn't copied that macro into your spreadsheet using Tools | Macros | Organise Macros, calling the function failed as without the macro, the function does not exist.

Using the macro organiser, I've copied the macro into your sample spreadsheet [attached] and now the #NAME error no longer appears.

Here is a search link for more basic information about OO macros.

[imadumby]

I totally missed it and wouldn't have known if you never said anything.
That was a great help (THANK YOU!) but unfortunately I still haven't been able to make it work.

This thread has become too long and confusing.
So to simplify it I am posting a new thread that has a spreadsheet that has the example that I want to work (but cant make it work) along with the macro on it, and also Lupp's solution (which I cant make work either.)

Hopefully it will be easier and maybe some other people will take a new look at it.

It can be seen here:
viewtopic.php?f=9&t=105417
The title is “JUMP! Go ahead and (help me) JUMP!

And my new spreadsheet example is here: