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 ?
And as an answer has to have more than 10 characters I’m adding this otherwise senseless sentence here
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)
- 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
Find & Replace
- Search for:
- Replace with:
- Other options: check,
- Current selection
- Regular expressions
- Click Replace All
If this answers the question, thank you to click on the to mark it ANSWERED.
The answer by @erAck is surely correct.
Workarounds will exist, however. I think we may even simplify the suggestions by @PYS , 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.
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: 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.
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”.)
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…
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:
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. 18.104.22.168 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)
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:
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. 22.214.171.124.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.
You are describing something different, and it’s not an answer to the original question.
the original question was: ‘can auto recalculate be turned off for some cells but not all ?’
the problem i mention produces exactly that result,
try the following:
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 (126.96.36.199.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
That’s a bug, not a solution.
you got it!!!
if you take it i’d suggest to take a look at the changes between 188.8.131.52 and 4.2.x.x,
@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.
i added the part between [edit:] and [/edit:], i’d put in the front because it is more suitable for intentional use