Ask Your Question
0

can auto recalculate be turned off for some cells but not all ?

asked 2015-07-10 20:35:25 +0200

Pete of Ebor gravatar image

I have worked out how to turn auto recaculate on and off by using Tools > Cell Content > Autocalculate, but is it possible to turn off autocalc for a single column / single row / single cell on a sheet while keeping it on for the rest of the sheet ?

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2015-07-10 21:07:01 +0200

erAck gravatar image

No.

And as an answer has to have more than 10 characters I'm adding this otherwise senseless sentence here ;-)

edit flag offensive delete link more
0

answered 2015-07-12 22:11:04 +0200

Lupp gravatar image

updated 2015-07-12 22:19:30 +0200

The answer by @erAck is surely correct.

Workarounds will exist, however. I think we may even simplify the suggestions by @pierre-yves samyn , at least under the condition of 'Iterative References' being permitted. We then can use a circular reference for keeping a former value. A conditional expression testing a trigger will suspend the recursion and temporarily apply the proper formula for recalculation of the value of the cell concerned if ordered.

See attached example for details.

ask53043RecalcRangesOnCommandOnly001.ods

edit flag offensive delete link more

Comments

hi, you are simulating! the exclusion from autocalculation, my script really excludes cells from it :-) regardless anyone might like to use that bug for anything, i'd like to get it killed!

newbie-02 gravatar imagenewbie-02 ( 2019-03-07 14:49:14 +0200 )edit

@newbie-02: Assuming your original suggestion was basically the same as found after your editing, I wonder if you considered for what reason @erAck called its effect a bug.
As I see it: Formulas not allowing to uambiguously identify their dependencies should be recalculated on any input/edit like formulas using volatile functions. If your workaround does as you claim this shows a violation of the mentioned rule, either for this specific kind of user function or for user functions without any explicit reference generally. Violation of rules is a bug in software development.
BTW: Your function as it is shown today will never return a number. Even if the accesed cell contains a number or its formula result is a number, your function will return the text (more precisely: its string) displayed in the cell.

Lupp gravatar imageLupp ( 2020-01-19 12:38:09 +0200 )edit

(continuing)
Here we have a borderline case: The formula tokenizer does not look into the code of the user function. The formula seeming to depend only on a few constants (like a call to SIN() with a constant argument) actually depends on changeable contents/resultts the constants are pointing to. Your workaround may be called "by hiding the dependency". In fact I don't know if there anywhere is a clear specification about how to handle such a case. If not we might call it a bug of the "specification" or of the development policy. Anyway the ODF specifications dont't mention user functions at all, afaik...
(M;y old workaround might be called "by overridable selfreference".)

Lupp gravatar imageLupp ( 2020-01-19 13:05:49 +0200 )edit

(continuing)
Anyway the answer by @erAck is correct.
Your suggestion as well as mine are workarounds not excluding any range from the automatic recalculation, but relying on either a user function and a rather complicated way to replace "ordinary references" (yours) or on a rather complicated "envelope" for ordinary formulas and a helper for triggering, but avoiding the need of user code (mine), setting recursion enabled in addition..

Lupp gravatar imageLupp ( 2020-01-19 13:13:23 +0200 )edit
0

answered 2015-07-12 09:15:04 +0200

pierre-yves samyn gravatar image

Hi

The answer (no) has already been given, but it is possible to simulate what you want by a formula. In the following example column B is calculated (A * 2). We want to turn off the automatic calculation for the range B5:B9 (green)

screenshot

  • E1: TRUE to calculate B5:B9, FALSE not to calculate it (screenshot displays "FAUX" because my interface is in French).
  • C5:C9 copy-paste only the value of B5:B9

The idea is simple if E1 = FALSE, B5:B9 must return the content of C5:C9, otherwise perform the initial calculation (A5:A9 * 2)

Of course one does not change one by one the formulas. Using the Find & Replace:

  • Select B5:B9
  • Edit Find & Replace
  • Search for: (=)(.*)
  • Replace with: =IF(\$E\$1;$2;OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));0;1))
  • Other options: check,
    • Current selection
    • Regular expressions
  • Click Replace All

RecalculateOff.ods

If this answers the question, thank you to click on the ✔ to mark it ANSWERED.

Regards

edit flag offensive delete link more
-1

answered 2019-03-02 07:50:09 +0200

newbie-02 gravatar image

updated 2020-01-19 09:38:35 +0200

[edit:]

i found one more - more handy? - possibility (to fulfill the OP request):

wrap the result you want into the excluded cell into a 'macro-function', e.g.:

Function GETCELL(sheetName As String, cellReference As String)
   oSheet = ThisComponent.Sheets.getByName(sheetName)
   oCell = oSheet.getCellRangeByName(cellReference).string
   'Msgbox(oCell)
   GETCELL = oCell
End Function 'getcell

will pull the 'string' of A1 into another cell with:

=GETCELL("sheet1"; "a1")

but will not recalculate until you press ctrl-shift-F9, or F9 while in cell A2, or - maybe, not tested - have activated recalc on load, or do 'nonchanging edits' on the pulling cell. ('nonchanging edits''? add a space somewhere in the formula and delete it while still in edit mode, delete the formula and press ctrl-Z for undo, reenter the same formula and similar)

last tested with ver. 6.4.0.1 winx64

beware: it may happen that 'more complex' constructs will not recalculated even if you want it,

BEWARE!!!: it may be that some users or developers consider this a bug and change the behaviouer in future releases.

(i asume it's difficult because the developers avoided it since years)

[/edit:]

hello all,

sorry for objecting, but the answer "no" is definitively not! correct,

yes!, the answer "no" is wrong!, the answer "yes you can" is better, at least for some versions of the program.

lemme show how,

sorry for working up a zombie thread, be prepared to meet an old and ugly monster ...

i think it's not intentional and not "by design" but "by lazy programming",

a friend of mine called it "not a bug, it's a feature" ... she's a programmer ;-)

starting somewhere around the change from 4.1 to 4.2 some funny things happened with the handling of similar formulas in rows beneath each other (shared formula groups"?), and one of these things is that autocalculate is "turned of" for a cell with a formula referencing a range, whenever you edit one of the copies! of that cell, as well as for the top "affected" cell of a group of cells with similar formulas whenever you change (move, delete or "undo") cells in the range used as source for the formula.

i'd file bugs:

https://bugs.documentfoundation.org/s... and
https://bugs.documentfoundation.org/s...

about that. in the descriptions of these bugs you can find scripts and samples how to exclude at least on cell from being autocalculated.

as it's considered being a bug i suggest not using it as a feature, hopefully it'll be gone in future versions, up to dev. 6.2.2.0.0 from 2019-02-27 17:10:55 it's still present.

as i'd like this bug being killed out as fast as possible - it scrapped the reliability of my work, caused me headaches and much irritation and many bug reports by others, and is wasting time of programmers and debuggers going for a dog instead of the fox - i offer a prize of 100 EUR for the first one correcting ... (more)

edit flag offensive delete link more

Comments

You are describing something different, and it's not an answer to the original question.

erAck gravatar imageerAck ( 2019-03-04 23:58:24 +0200 )edit

@erAck the original question was: 'can auto recalculate be turned off for some cells but not all ?' the problem i mention produces exactly that result, simple sample: try the following: A1: "=SUM(B1:C1)" B1: "1" C1: "2" mark range A1:C1 and copy it to A2:C2 move B1 to B2 either by dragging with the mouse(click - shift-click - drag) or by ctrl-x ctrl-v, recalculate the results in A1 and A2 with pencil and paper, in my case (6.3.0.0.alpha0) they mostly fail and A1 and A2 become excluded from autocalculate, you may enter whtever you want in B1 and B2. that remains over correcting the result with ctrl-shift-F9, autocalculate is still 'off' for A1 and A2 on subsequent input to B1, B2, C1 or C2

newbie-02 gravatar imagenewbie-02 ( 2019-03-05 13:02:26 +0200 )edit

That's a bug, not a solution.

erAck gravatar imageerAck ( 2019-03-06 14:15:02 +0200 )edit

you got it!!! :-) :-) :-)

if you take it i'd suggest to take a look at the changes between 4.1.6.2 and 4.2.x.x,

newbie-02 gravatar imagenewbie-02 ( 2019-03-07 07:18:07 +0200 )edit

@newbie-02This thread is nearly 5 years old, and your amswer was given nearly a year ago. Today I was notified of an activity in it, and found your answer editedthe relevant suggestion in a way not allowig for a immediate comparison with the previous vesrion.
This doesn't make sense, imo.

Lupp gravatar imageLupp ( 2020-01-19 12:21:31 +0200 )edit

@Lupp:

i added the part between [edit:] and [/edit:], i'd put in the front because it is more suitable for intentional use

newbie-02 gravatar imagenewbie-02 ( 2020-01-20 16:28:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-10 20:35:25 +0200

Seen: 1,505 times

Last updated: Jan 19