Page 1 of 1

Keep cell from updating when underlying data changes

PostPosted: Sun Jan 11, 2015 3:57 am
by rwwood
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.

Re: Keep cell from updating when underlying data changes

PostPosted: Sun Jan 11, 2015 4:11 am
by RusselB
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)

Re: Keep cell from updating when underlying data changes

PostPosted: Sun Jan 11, 2015 5:01 am
by rwwood
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.

Re: Keep cell from updating when underlying data changes

PostPosted: Sun Jan 11, 2015 7:41 pm
by MTP
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?

Re: Keep cell from updating when underlying data changes

PostPosted: Sun Jan 11, 2015 8:24 pm
by rwwood
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.

Re: Keep cell from updating when underlying data changes

PostPosted: Sun Jan 11, 2015 9:31 pm
by MTP
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)?

Re: Keep cell from updating when underlying data changes

PostPosted: Sun Jan 11, 2015 11:56 pm
by Lupp
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.

Re: Keep cell from updating when underlying data changes

PostPosted: Mon Jan 12, 2015 3:06 am
by rwwood
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.

Re: Keep cell from updating when underlying data changes

PostPosted: Mon Jan 12, 2015 3:45 am
by RusselB
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.

Re: Keep cell from updating when underlying data changes

PostPosted: Mon Nov 11, 2019 1:12 pm
by imnotmrfire
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 :-)

Re: Keep cell from updating when underlying data changes

PostPosted: Thu Nov 14, 2019 7:11 am
by RusselB
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.