How to create automatically updating column, which aggregates data from other sheets?

[TukeV]

Hello

TLDR: How to create a column, which automatically updates to have a new date I add elsewhere in the document?

---

I’m currently having a spreadsheet where I list my progressions in various exercises. I have dedicated pages/sheets for different kinds of exercies (bodyweight upper and lower body, dumbells exercises, etc.).

Here’s an example how a sheet for bodyweight exercies might look like…

Sheet1

Date	Push ups	Pull ups	Inv. Row
01.01.1970	4x15	4x5	4x10
03.01.1970	4x17	4x7	4x12
05.01.1970	4x20	4x10	4x15

Now I want to create a summary page where I can quickly glance what exercises I’ve done each day…

Summary

Date	Sheet1	Sheet2	Sheet3
01.01.1970	x
02.01.1970		x	x
03.01.1970	x	x	x
04.01.1970		x
05.01.1970	x		x

I was able to initialize the Summary.Date column by first consolidating date columns from my existing sheets, sorting the resulting column and then removing all the duplicates.

Next, I would like to automate the updating process so that whenever I update an exercise sheet (let’s say. I add date and few exercises to Sheet3), the Summary page would automatically update too.

I have already automated the X markings on the summary page so that whenever I add a date to the Summary.Date column, an IF statement will check if the date can found from the corresponding Sheet and adds the X mark if the date is found.

However, I haven’t managed to automate the date part yet and fankly I’m not quite sure how to I approach this.

So, how I can I make the Summary sheet’s date column automatically update, whenever I write a new entry to any my actual diary sheets?

[joshua4]

It’s always nice if you can upload an example. Using your description of what you wanted, though, I didn’t see any reason to stop at just having X’s. The attached ODS shows how to display your actual reps on a summary page. Just click on the down arrow beside Date and select “Sort ascending” to sort the dates. The other arrows aren’t very helpful, but this uses Data>Autofilter, which will allow filtering on every column. If Autofilter gets turned off, just click anywhere in the summary table and use that menu sequence to turn it back on.

Honestly, what we are doing here is precisely what the SQL language is meant to do accessing a database. In other words, really you want a database (Base) for this, not a spreadsheet.

UniqueDateSummary.ods (34.4 KB)

[TukeV]

I think you misunderstood my question and the spreadsheet you attached is only yielding #VALUE! erros on my end.

Also, I’m currently quite satisfied how summary page looks like and I don’t want to change it’s structure.

The only thing I want is that Summary.Date should update automatically whenever I add an entry to either Sheet1 or Sheet2. The data on the Sheet1 or Sheet2 is irrelevant.

To put it as simply as possible:

1. I add a date to either Sheet1 or Sheet2. What I should do that this date will automatically appear in the Summary.Date too whenever I make these edits?

2. I add same date to Sheet1 and Sheet2. How to make sure this will not appear twice on the Summary.Date?

I would rewrite the original question now since it’s causing confusion with irrelevancies, but apparently I don’t have rights to even edit my own posts.

[joshua4]

Seldom will an answer be turn-key. It is up to you to take the pieces that you need or want, especially if you do not upload an otherwise complete example to begin with. Just use the UNIONUNIQUE macro part from the example I gave. Mimic the Date sheet in the example, then you can reference that Date sheet, sort, or do whatever you want.

I tried downloading and using the spreadsheet without getting any #VALUE! errors. If you can be specific about what cells are showing errors then we could look at what is going on. Do you have macros enabled under Tools>Options>LibreOffice (in the left list)>Security (sub-listing in the left list)>Macro security (a button)? The solution I gave uses a User Defined Function (UDF), or “macro.”

[TukeV]

Here’s a dummy sheet to illustrate the problem. I honestly thought that the question was simple enough so that I thought I don’t need to upload anything, but I guess “show, don’t tell applies to technical problems too…”

Or maybe itäs because english is my 3rd language.

In anycase here’s a dummy version of where I’m at atm.

question.ods (8.6 KB)

---

Thanks for pointing out the macro permission part. That was the problem which caused the error messages to appear (the whole bl***y summary sheet was filled with errors).

I have never done anything with the libre-macros though, but that Union unique sounds like the thing I want to do here. Is this a predefined function or something you wrote for the example? It looks custom made, but I just want to be sure.

---

Also… gotta love how the forum software doesn’t allow line breaks in the comments and how everything is concatenated into nice soup of words. Dyslexia friendly design for sure <3

[Wanderer]

@TukeV

gotta love how the forum software doesn’t allow line breaks in the comments

Line

breaks

are

possible.

And the software “improves” your quotes…

[TukeV]

Line

breaks

are

possible.

And the software “improves” your quotes…

Interesting, on Firefox the text is actually mashed together, but on Chrome the paragraphs are formatted with proper paragraphs as intended.

Fascinating

[joshua4]

@Wanderer, if you are saying a person can do something, then why not say how? Did you just use <br>. If so, why not share?

[joshua4]

@TukeV, here is what you seem to want based on your upload. That is not the same thing as the tables you presented in the OQ, where you had multiple columns on each of the separate raw data sheets.


In any case, this is in fact just as I said…all I did was take the Dates sheet and UNIONUNIQUE macro (yes, I wrote that…) and combined them quickly with your example ODS. Notice that I did need to add a check for blank date cells in the date column. To see this, look at the formula in, for example, Summary.$B$2, where I added an AND operation around the COUNTIF you had there already.


Now that you mention dyslexia, I see why you want less information in the summary sheet. In any case, this does the trick…and certainly the formula is easier! Notice, however, that the sheet names are hard-coded into each column’s formula, which my original version avoided.


SimpleUniqueDateSummary.ods (18.6 KB)


I only copied the formulas on the Summary sheet down as far as the yellow region. The Dates sheet has its array formula going down to about row 400–so just over a year’s worth.

[Wanderer]

@joshua4

Did you just use
. If so, why not share?

Sorry - even more simple: I cheated with just two returns. But as it was already shown by @TukeV : It depends on your browser… (And on my mobile it is firefox showing separated paragraphs and Chrome only displaying a line-break.

So now I have no clue to post anything reliable here, except with triple quotes.

But I looked up markdown syntax:

Line Breaks
To create a line break or new line (<br>), end a line with two or more spaces, and then type return.

So
I
will.
try.
Annoying: If I type the second space, I get an inserted period.

[TukeV]

In conclusion: the answer to my original question is that there really isn’t a built-in functionality or off-the-shelf way to do what I wanted, but it can be accomplished by creating a macro routine.

At the time of writing I haven’t really looked into macros, so I still need to figure out how I can import that routine into my own document and how I can use my own macros like a function, like what did in your example.

But I believe that’s something I can find out myself by reading more about the macros and their usage in general and possible future questions regarding macros deserve their own thread if needed.

Thanks for the help.

[joshua4]

Correct. Even in Excel 365 where you have SORT and UNIQUE built-in, combining ranges is a pain. The way spreadsheets were originally meant to be used combining ranges was done by manipulation, not function. Databases were–and still are–the tool to use when combining ranges.


You can use Tools>Macros>Organize macros>Basic>Organizer (button) to add a module to your worksheet. At the dialogue this sequence brings you to, click on your ODS file name in the list then click the New button. Accept Module1 by clicking Ok. Now exit out of all that stuff. Then Tools>Macros>Edit macros. Now pick out your ODS file’s new Module 1 in the list at the left. You are where you need to be to type in a macro at the right. Of course, what you may want to do here is also have the example ODS open, go to it on the left, copy all of the functions there (everything), then go back to your ODS in the list at the left and paste in the UNIONUNIQUE and ADDRANGE… (everything) there.


Also notice that you have to enter the UNIONUNIQUE function (as on the Dates sheet) using ctrl+shift+enter, that is, as an array formula.


Honestly, if I were you, I wouldn’t spend much time trying to learn LO BASIC or macros at all. As some of the Excel 365 features get implemented they will become less necessary, BASIC is easy but the API you use to talk to LO is not, and there is very little benefit to be derived in any other way. You are much better off learning to use Base to talk to a database and start thinking in terms of tables/queries/reports. That would pay off in spades–and the community will fall over itself helping you on a little BASIC or SQL to tie things together if needed. No one likes to spend time on uphill battles in Calc when it isn’t really the tool for the job in the first place.

[Wanderer]

@TukeV

there really isn’t a built-in functionality or off-the-shelf way to do what I wanted,

Just a reminder: Base IS a built-in functionality of LibreOffice. In Excel you often have no Database, as you have to pay for “Access” extra. So Excel needs more functions to cope for this.