Ask Your Question

How do I grab data from an Automatic Updating Cell (=NOW()) and put it into a different cell and not have it update?

asked 2016-12-11 08:04:06 +0100

MrTravisL gravatar image

I know this is probably not possible, but hey I've done some pretty amazing things with Calc so it's worth asking. What I am trying to do is hard to explain so i'll just give an example.

Cell A1 is =NOW() - Cell B1 is =A1 So lets say A1 says: December 1, 2016 5:55 AM (the current time), and I put in =A1 to B1 (which would grab the December 1, 2016 5:55 AM and put it into B1). What I want to happen is, lets say in 10 minutes when NOW() updates to December 1, 2016 6:05 AM, I want B1 to STAY at December 1, 2016 5:55 AM and NOT change to the current time.

Basically what I want to happen is record the time when I put the formula into the cell and not update anymore. I know this could all be avoided by just typing in "December 1, 2016 5:55 AM" but with what I am working on that actually takes quite a bit of time.

edit retag flag offensive close merge delete


It's worth to understand what is the moment when you want to record the time in B1. What is that special in the second when you have pressed Enter in B1's formula? I mean, wouldn't that be better if you made a pushbutton and a macro that would update B1 with static value of current time instead of formula (and possibly refresh A1, if required)?

Also, depending on your workflow, you could use insert special (Shift-Ctrl-V) to paste to B1 after you copied A1. There, you could deselect Formulas

Mike Kaganski gravatar imageMike Kaganski ( 2016-12-11 08:38:19 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-12-11 09:29:05 +0100

pierre-yves samyn gravatar image


@Lupp proposed a smart use of circular references here (question was tagged "writer" but it is a Calc solution).

This solution also described in the FAQ.


edit flag offensive delete link more


I would emphasise again that the mentioned solution should only be considered in noncritical cases. It will not make any spreadsheet history aware. The information you create and keep this way once spoilt by any event cannot be retrieved. In specific any measure causing the information to change cannot be made undone by Ctrl+Z. You cannot even set it back manually to the previous value if you still know it. Thus the information is not actual data at all.

Lupp gravatar imageLupp ( 2016-12-11 11:58:21 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-12-11 08:04:06 +0100

Seen: 37 times

Last updated: Dec 11 '16