Ask Your Question
0

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

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

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

3 Answers

Sort by » oldest newest most voted
0

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

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
0

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

Lupp gravatar image

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

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
0

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

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

Question Tools

1 follower

Stats

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

Seen: 618 times

Last updated: Jul 12 '15