Lupp wrote:"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.
Lupp wrote:"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.
Lupp wrote:"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.
Lupp wrote:"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.
Lupp wrote:"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?