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
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

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 both bugs and providing a detailed description what went wrong in which 'commit' to the code. point of proof and crucial point in time is the timestamp the analysis and correction (correction for as well the "first affected version" as for the newest dev-version) is placed in the comments of the abovementioned "bug discusssions". it's a voluntary offer subject only to my decision and excluded from legal claims.

the solution must not be "plastering" the symptoms with workarounds, but a clear unveil of the core of the problem, e.g. something like - in module x, lines yyyy to zzzz a loop for recalculation needs to run from 0 to max but the running variable "i" is incremented before using it inside the loop thus starting processing with "1" instead of "0" - would be sufficient for me (things like that happened in former times ...)

besides i'd like everybody to play with the problem and to provide additional info, traps like this shouldn't be in programs for productive use and shouldn't reside there for years.

regards

newbie-02

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 816 times

Last updated: Mar 02