Is there a way to do this other than copy/paste special?

Discuss the spreadsheet application

Is there a way to do this other than copy/paste special?

Postby TKelly » Fri Jul 13, 2018 12:21 am

I would like to find a better way to save, each day, a copy of a row of data called "Daily Totals" from one sheet to another without using copy and 'paste special'

I made a spreadsheet to calculate daily food intakes of calories and nutrients, with the bottom row dispalying the "Daily Totals" of calories, macros, nutrients, vitamins etc.
I use the same sheet each day, overwriting the previous entries, keeping only a copy of the bottom row ('Daily Totals').

I use copy and 'Paste Special' (to a another sheet) to keep a record of the bottom row ('Daily Totals') each day.

Is there a way to populate cells in successive rows in a seperate sheet, with the numbers (not formulae) without using 'Paste Special' ?
OpenOffice 4.1.3 on Windows XP
TKelly
 
Posts: 4
Joined: Thu Jul 12, 2018 7:25 pm

Re: is there a way to do this other than copy/paste special?

Postby Zizi64 » Fri Jul 13, 2018 7:46 am

Is there a way to populate cells in successive rows in a seperate sheet, with the numbers (not formulae) without using 'Paste Special' ?


Yes, there is (there are).

My first tip:
The Macros. (It is not a simple ways for a beginner. You must write these macros (but not record them) based on the API function. API: Application Programming Interface)
You can copy the desired data values (without formatting properties) values into the first empty row of the another sheet by some macro code).

My second tip:
Database programming (I never used the Base application, therefore I can not help you about the using of the Base.)
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.5 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6886
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: is there a way to do this other than copy/paste special?

Postby Villeroy » Fri Jul 13, 2018 11:00 am

There should be no need to store calculated results as a copy anywhere else. For a dynamic table of daily results you can use the data pilot. A database would be much easier to handle over the years but more difficult to create.
Attachments
kcal_pivot.ods
(70.87 KiB) Downloaded 5 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25451
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: is there a way to do this other than copy/paste special?

Postby TKelly » Sat Jul 14, 2018 5:25 am

Zizi64 wrote:
Is there a way to populate cells in successive rows in a seperate sheet, with the numbers (not formulae) without using 'Paste Special' ?


Yes, there is (there are).

My first tip:
The Macros. (It is not a simple ways for a beginner. You must write these macros (but not record them) based on the API function. API: Application Programming Interface)
You can copy the desired data values (without formatting properties) values into the first empty row of the another sheet by some macro code).

My second tip:
Database programming (I never used the Base application, therefore I can not help you about the using of the Base.)


Thanks for your reply.
I've never used Macros, I was trying to avoid that for the moment...
I will look at the Database way of doing it - I suppose it makes more sense really.
OpenOffice 4.1.3 on Windows XP
TKelly
 
Posts: 4
Joined: Thu Jul 12, 2018 7:25 pm

Re: is there a way to do this other than copy/paste special?

Postby TKelly » Sat Jul 14, 2018 5:43 am

Villeroy wrote:There should be no need to store calculated results as a copy anywhere else. For a dynamic table of daily results you can use the data pilot. A database would be much easier to handle over the years but more difficult to create.

Thanks for your reply.
The way I do it at the moment is by copying the bottom row of values (the daily totals of calories, nutrients etc) and then 'paste special' to the next available row on another sheet ( which acts as my table or list).
This way, (using paste special) only the numbers get saved in the 'table' sheet, not the formulas.
I have to keep a copy of the daily totals, because I re-use the same sheet where I input the food every day, so it gets overwritten.

I wanted a way to avoid copy/paste (tedious as there's more than one person's data)
OpenOffice 4.1.3 on Windows XP
TKelly
 
Posts: 4
Joined: Thu Jul 12, 2018 7:25 pm

Re: is there a way to do this other than copy/paste special?

Postby Lupp » Sat Jul 14, 2018 10:46 am

[As I read the question, the user doesn't want to keep the details, but only the daily summaries. This isn't the database-do, of course, but it's not unreasonable at all (small filesize, low complexity, efficiency on rexalculation/refresh).]

There is no preconfigured tool doing exactly as you want and, as Zizo64 already mentioned, you won't get the functionality by simply recording a "macro". However, it can not only be done by user code directly accessing the API, but also by "tweaked" code based on recording.

I tend to assume you do not want to learn much about macros. But if you want to start the long way now, you may see what you can get by studying the attachment below.
To run the macro from the document you need to permit macro execution when loading. You are only prompted for this if your macro security is set to "Medium". (NEVER set "Low"!) To only study the code the permission is not necessary.

BTW: Don't summarize your daily data below the detail rows, but above. It's much more practical.

For users if LibreOffice: There is a strange bug in recent versions resulting in malfunctions with the uno-command 'Copy' if the routine is first called from the PushButton. (It's a miracle to me.) AOO is not afflicted.
Attachments
exp.ods
(21.28 KiB) Downloaded 7 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1876
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Is there a way to do this other than copy/paste special?

Postby Villeroy » Sat Jul 14, 2018 4:47 pm

You must not split data in separate lists. This is always a mistake. Put everything in one list and let the software do the rest as demonstrated in the attachment of my previous posting where the first sheet consists of arbitrary dummy data (dates and kcal for 2 persons) in random order. You can do what you want with that list. You may sort it any way you want. You may apply filters. You may add formulas.
From any aspect of that single list you can derive a list of daily results. This is the pivot table on the second sheet. It shows the daily results for each person and for each day semi-automatically. The derived pivot table is refreshed by right-click>refresh or menu:Data>Pivot>refresh
If you want the results for months: Click any date cell in the pivot and hit F12 (menu:Data>Group). Choose months any years and you get the sums of kcal for each person and for each year and each month.
Pivot tables (aka "data pilots") provide a lot more options: https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25451
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is there a way to do this other than copy/paste special?

Postby TKelly » Sun Jul 15, 2018 4:48 am

@Villeroy , @Lupp , @Zizi64
Thanks for the example files, and for your input, I'm looking at what you've posted.
As a beginner, I'm going to have to educate myself a bit further on the database aspects and also look at Macro usage.
OpenOffice 4.1.3 on Windows XP
TKelly
 
Posts: 4
Joined: Thu Jul 12, 2018 7:25 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 47 guests