Keep cell from updating when underlying data changes

Discuss the spreadsheet application

Keep cell from updating when underlying data changes

Postby rwwood » Sun Jan 11, 2015 3:57 am

Is there a way to use a formula that pulls data from another tab in such a way that, once the cell is populated, its contents won't change when the cell it was originally pulled from changes?

In other words, I have data in sheet1.A1 that is dynamic, that is it changes from one day to the next. I want to be able to use a formula to copy the data from sheet1.A1 to sheet2.B2 in such a way that sheet2.B2 doesn't get updated when sheet1.A1 changes.

I'm a novice, so I don't know if this can be done or if it can where to start.

Thanks.
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: Keep cell from updating when underlying data changes

Postby RusselB » Sun Jan 11, 2015 4:11 am

To my knowledge this can only be done by using a combination of Copy and Paste Special (Numbers checked, formulas unchecked)
In Windows, and using OpenOffice (so I don't guarantee exact matches), this is done by using Ctrl+C (Copy) and Ctrl+Shift+V (paste special)
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5978
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Keep cell from updating when underlying data changes

Postby rwwood » Sun Jan 11, 2015 5:01 am

Yeah, that would work if I wanted to copy the correct value every time I needed it in a new row, but I'm really hoping there's a way to do this programatically.
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: Keep cell from updating when underlying data changes

Postby MTP » Sun Jan 11, 2015 7:41 pm

I'm not entirely understanding what you are wanting to do (although I have a suspicion a database application would be better for you than a spreadsheet). Could you upload an example file to help us understand what you need?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Keep cell from updating when underlying data changes

Postby rwwood » Sun Jan 11, 2015 8:24 pm

Not really possible to upload a sample file since I don't have it entirely set up. Essentially, there is a table of dynamic data on one sheet that is being pulled from a web site. It may change several times a day for some rows, of which there are roughly 30. I want to be able to progamatically copy the value from cells in that table to cells on another sheet in such a way that only the value is copied. I know that I can do it manually with copy/paste special, but that means sorting through the first table to find what I need and then copying it to the second sheet. I already have a lookup formula set up to do the copy based on the value of a static column in the table on sheet1, but of course every time the dynamic data updates, the data that I've already entered into sheet3 also updates. I don't want that. I want it to remain the value that it was when originally entered.

I hope that helps to clarify.
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: Keep cell from updating when underlying data changes

Postby MTP » Sun Jan 11, 2015 9:31 pm

I may still not be understanding, but - Can't you copy/paste special your lookup formulas (i.e. NOT having to sort through the first table)?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Keep cell from updating when underlying data changes

Postby Lupp » Sun Jan 11, 2015 11:56 pm

This should be a question I know as recurring from time to time in some forums, in specific in libreofficeforum.org and in ask.libreoffice.org. I think in this forum, too. Out of pure and principal interest - not intending to apply the solution - I experimented hours about it one or two years ago.
A quick result was: It can be done using a concept I named "conditionally suspended circular references" relying on the capability of AOO/LibO to accept formulae referring directly or indirectly to the cell containing the formula itself - and to abandon a resulting iteration after a maximum number of steps (See options!).
On the long term the result was: I should strongly dissuade users from applying the concept. It is clearly too fragile and not reliable enough for actual work. This in specific because there will sometimes be need for enhancements/scaling up/other maintenance of the sheets that cannot be done without switching off the feature or at least putting its working at risk. Reinstating the sheets after any malfunction won't work. Undo will be not working for the cells concerned.
As you were already advised: Shift to a database!
If this is beyond your power you may have one of the exceptional cases where a solution by programming a custom sub is preferable.

If you want to experiment yourself evaluating my advice you may start with the tiny example I provide in the attachment. Please take my warnings seriously.
Attachments
ooo74605PreservePreviousValue001.ods
(18.78 KiB) Downloaded 39 times
On Windows 10: LibreOffice 6.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2802
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Keep cell from updating when underlying data changes

Postby rwwood » Mon Jan 12, 2015 3:06 am

Is there a way to convert the Calc sheet on which I've spent considerable time to Base? I know that it would mean setting up forms for entering the data into tables, and much of the tabular data I have in Calc uses complex formulas to populate the columns.

In the mean time, the one solution that I came up with and which I'm going to experiment is to set up a macro to copy the contents of the cell that has been pulled from the dynamic data, and then paste it back into the same cell without the formula. I want have the macro fire on an onChange event so that when data is first entered into that cell by it's formula, the data is copied and pasted back into the cell without the formula. Hopefully that will accomplish what I want it to.

In reality, a database form for entering the needed information and reports to spit it back out again would be a much more elegant way to do what I'm doing.
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: Keep cell from updating when underlying data changes

Postby RusselB » Mon Jan 12, 2015 3:45 am

Yes it is possible, and may even already have been done.
Open your spreadsheet in Calc then press F4
Take a look at the list of registered databases.... Is your spreadsheet listed?
If so, then your spreadsheet is already registered as a database.
If not, then you can register it.. regretfully I'm having a bit of a "blonde moment" (no insult intended to any that are blonde), and can't remember how to do that.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5978
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Keep cell from updating when underlying data changes

Postby imnotmrfire » Mon Nov 11, 2019 1:12 pm

mmmm..
1: Select the cells/row(s)/columns(s) you want to remain static after population
2: Go to Tools/Cell Contents and uncheck >Autocalculate
3: That should be it, Give it a shot and see what happens :-)
OpenOffice Calc - OpenOffice 4.16
Win10x64(1903)
I am fox ????
imnotmrfire
 
Posts: 1
Joined: Mon Nov 11, 2019 9:54 am
Location: Sparks, NV

Re: Keep cell from updating when underlying data changes

Postby RusselB » Thu Nov 14, 2019 7:11 am

Auto-Calculate affects the entire spreadsheet, not a specific range.... or at least that's how it appears in my testing.
If you wish to dispute this, please upload a .ods file that you have tested and shows that the Auto-Calculate is range specific.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5978
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 7 guests