[Solved] Saving a static version?

Discuss the spreadsheet application

[Solved] Saving a static version?

Postby miasma.gajs » Tue Jul 28, 2020 11:10 am

Hopefully a quick answer to this one, otherwise I will diligently RTFF :

I have a spreadsheet that has lots of dynamic content (e.g. TIME(), DDE look-ups, etc.). I want to be able to save versions of it at a point in time, so that all the dynamic data becomes static.

Is there a straightforward method to achieve this please?
Last edited by MrProgrammer on Mon Aug 10, 2020 3:16 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Apache OpenOffice 4.1.1
MS Windows XP Pro v2002 SP3
MS Windows 7 Pro SP1
miasma.gajs
 
Posts: 19
Joined: Thu Sep 18, 2014 1:18 pm

Re: Saving a static version?

Postby RoryOF » Tue Jul 28, 2020 11:25 am

Possibly use /File /Export as PDF.
Apache OpenOffice 4.1.9 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32194
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Saving a static version?

Postby Lupp » Tue Jul 28, 2020 12:01 pm

You (the OQ) talked of a single spreadsheet. I would assume you also would want to keep the sheet with the original formulas.

How I would do it:

1. Use MoveCopy from the context menu of the seet's tab to create a copy of the sheet including all formatting.
2. Go back to the orginal sheet.
3. Select All (Ctrl+A by default)
4. Copy (via the menu or Ctrl+C)
5. Got to the newly copied sheet, cell A1
6. Paste special, (Ctrl+Shift+V), only constant content (no formulas) selected.

(Conditional formatting, validity settings, and the like may contain formulas that will be kept tis way.
Such formulas shouldn't contain links and/or volatile functions.)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3002
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Saving a static version?

Postby miasma.gajs » Tue Jul 28, 2020 4:04 pm

Thanks for suggestions. Was hoping there would be a single function that would achieve this. Suppose I could knock up a macro.
Apache OpenOffice 4.1.1
MS Windows XP Pro v2002 SP3
MS Windows 7 Pro SP1
miasma.gajs
 
Posts: 19
Joined: Thu Sep 18, 2014 1:18 pm

Re: Saving a static version?

Postby Lupp » Tue Jul 28, 2020 5:56 pm

I'm not exatly what you actually want to achieve.
Saving data and formula results the same way without distinguishing both cases and without any formatting would best be done to a csv-type file per sheet.

Or do you want to be able to resume calculations?

Just snapshots?

Snaposhots with formatting? (Like what I already suggested.)

Snaposhots with formatting, but without CF and Validity settings? (More complicated. Recommendable or necessary probably if formulas used inside CF or Validity definitions may contain links and/or volatile functions.)

Many sheets at a time?

What I already described can rather easily be delegated to a macro. You might even record one - and probably enhance it a bit by manually edited code.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3002
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Saving a static version?

Postby RoryOF » Tue Jul 28, 2020 5:59 pm

My understanding of a "static version" is that it is a snapshot at a point in time, not capable of any calculations. A PDF should meet the need.
Apache OpenOffice 4.1.9 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32194
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Saving a static version?

Postby Lupp » Tue Jul 28, 2020 6:20 pm

I wouldn't argue much about this.

However, a "static version" may also be expected to preserve the structure to some degree, probably for linking to the saved data later from elsewhere, compare a sequence of snapshots by automated means, ...

The basic structure of spreadsheets is "cellular" with "addressable" pieces of information.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3002
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Saving a static version?

Postby miasma.gajs » Wed Jul 29, 2020 2:35 pm

Thanks.

With respect to the debate on just what "static" means in this case : Yes, preserving spreadsheet functionality would be desirable for future extract and analysis; hence PDF is last resort, copy-and-paste-data-only is probably best suggestion to date.
Apache OpenOffice 4.1.1
MS Windows XP Pro v2002 SP3
MS Windows 7 Pro SP1
miasma.gajs
 
Posts: 19
Joined: Thu Sep 18, 2014 1:18 pm

Re: Saving a static version?

Postby John_Ha » Wed Jul 29, 2020 3:26 pm

miasma.gajs wrote:preserving spreadsheet functionality would be desirable for future extract and analysis

Depending on what dynamic content you have in the spreadsheet this may (or may not) work.

Identify all the dynamic content by giving those squares coloured backgrounds. Even better would be to move all the dynamic content to one area of the spreadsheet where they are all together. Now copy from there back into the working area as, say, C5 = A100, where A100 contains the dynamic data.

1. Save as a new name so as not to corrupt the original.
2. Set the displayed places after the decimal point to be as large as possible in the all the dynamic content cells.
3. Overtype all the dynamic cells with their displayed values and save.

It shouldn't but be careful in case fixing one of the dynamic values affects any of the other dynamic values.

Be aware that a display of 0.33 is probably 0.3333333333333 so you will be introducing small errors. Lorentz did this with his weather forecast and discovered Chaos Theory.
Last edited by John_Ha on Wed Jul 29, 2020 5:02 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8215
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Saving a static version?

Postby MrProgrammer » Mon Aug 03, 2020 9:28 pm

miasma.gajs wrote:I have a spreadsheet that has lots of dynamic content (e.g. TIME(), DDE look-ups, etc.). I want to be able to save versions of it at a point in time, so that all the dynamic data becomes static. Is there a straightforward method to achieve this please?
You can create a scenario "Dynamic" which holds the current sheet with formulas. Then, to preserve a static version of a sheet, select all its cells, Copy, Paste Special with Formulas unchecked, and create another scenario. Use the Navigator to load the desired scenario, either "Dynamic" or one of the static ones. You should be able to use this technique even if you have multiple sheets, though it might get messy.
Using “what if” scenarios

However, this only addresses volatile functions and links in cells. If you use them in conditional formats, validity settings, defined names, etc. the only simple solution is to save as PDF.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3908
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests