Store history of values from multiple cells in seperate sheet without overwriting

English
#1

So, as the title says. I’m looking for a way to have a sheet get the data of a few specific cells from sheet one whenever they are updated.

paste items 1 value in column B1, items 2 value in column D1 and so on.
And then when a new update happens to paste the new value in column B2, D2 and so on so i keep a history so i can use the values as an average and change over a longer period of time.

I’ve tried to understand how to use some macros that are already out there but i can’t understand where to put sheet 1 and specific cell and which line is for pasting it into different sheet and cell.

#2

You should use a shorter title such as “Macro to record list of changing values” and put your description into the body of your question. You can edit your question to change it.

#3

updated it to be shorter and more accurate.

#4

Thank you, really short. But, excuse me, this has not become clearer. It’s embarrassing that the target cells are not in a row, you mention B, D, etc. What about columns A, C, E, G? … You know, it is better to see a sample once, an example of a spreadsheet, than to read its verbal description many times.

#5

A C and E are used for the name or something else to signafy what the value in B D and F are suppose to represent

Here is kind of a visual way so it is easier to explain and show why some cells are not to be stored.
sheet 1:

  |     A      |  B |     C      |  D |      E     |  F |
1 | item 1     |    | item 2     |    | Item 3     |    |
2 |total Cost  | 50 | total cost | 60 | total cost | 75 |

So, i want to take the value of B2, D2 and F2
and then paste those into sheet 2 when they change.

Sheet2:

  |       A       |       B       |       C       |
1 | Item 1 values | Item 2 Values | Item 3 values |
2 |      47       |      59       |      71       |
3 |      52       |      63       |      73       |
4 |
5 | 
6 |

Here, i want to paste B2 from sheet 1 into A4 on sheet 2, D2 into B4, F2 into C4
Once they change again i want to take the same cell of sheet one, and paste those into sheet 2 again, however time time into A5, B5 and C5 as the others above are already in use.
Then once again into A6, B6 and C6 next time and so on.

I hope this explains it a bit better what my goal is.

I would have used screen shots but not allowed since it’s a new account and can only post screenshots when doing a new post.

#7

Thank you, it’s good that you provided the sample data - it helped to understand your desire. The solution could be like this - Demo_InputForm.ods (11.4 KB).
But I strongly recommend heeding @Villeroy’s advice - spend quite a bit of time mastering the Base component, you will greatly facilitate your life and solving such problems will be simple and easy.

#6

Input forms (boxes where you enter data) are part of the database component Base. You enter the data of one record (table row) and the data are inserted into a table.

#8

A database demonstrating a simple input/output system: https://forum.openoffice.org/en/forum/download/file.php?id=28761
The items are stored in table “Articles”, ingoing and outgoing items are stored in tables “Input” and “Output”.
The input forms are the user interface. There is one for new ingoing items, another one for new outgoing items and a third one where you can see the entered input and output.
The reports are for nicely layouted printing.

#9

I’ve experimented, issue is i can’t get it to be automated, as i need to import the database from my excel. but that one doesn’t update automatically unless i run the requery macro on startup.

Next issue is i can’t make a new database to only take out the specific cell data and store it.
Nor can i figure out how to the export it and import into my excel so i can see the average. I don’t mind having the programs open at the same time for it to do it’s thing.

#10

Well, then you have to do whatever you do with a bloody spreadsheet. No matter how much Basic code you throw at it, it will never work like a database. Setting up a database is by far easier than throwing thousands of lines of Basic code at the problem.

#11

I assume there is not way to make data base update it’s data source automatically? Say, reimport every hour?

I don’t mind if i have to import it back into the spreadsheet manually once a day or week.

#12

You MAY link a spreadsheet range or a pivot table to a database query and apply formulas, charts, (conditional) formatting and all the operations that Calc has to offer.
You can automatically refresh the linked database data when loading the spreadsheet.
You can even set a timer to refresh linked database data.

#13

I’m sorry but i can’t find anywhere to set a timer. Nothing on google.
I’ve set up a query of the database to get the specific columns i’m looking for. Tried making a form to display the results i want but doesn’t work.

I honestly need a guide on how to set this up, but there is nothing. Sadly i’m a visual learner as well.

#14

[Tutorial] Using registered datasources in Calc
It is not documented and not implemented iin the graphical user interface. There is an API for ithis and if you use it with caution (long intervals), it works just fine. You have to set the interval only once. The refresh interval is stored in the document and LO executes the refresh in the given time interval. (at least it did some years ago)

#15

There appears to be no solution to this, nothing has been helpful as i can’t get libreoffice base to automatically update my datasource i provide, let alone to make a table that then takes specific cells value to then output automatically and still needs to be done manually, so might as well just do it manually.

If there is someone for some reason that actually knows how get this working and that can explain, that would be amazing.

#16

Get working what? You lost me.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.