Ask Your Question
0

Combine multiple sheets into one master sheet [closed]

asked 2015-12-06 23:32:50 +0100

Mountain gravatar image

updated 2015-12-06 23:41:25 +0100

My question is similar to this one, but has additional requirements not addressed there:

[Calc] How to combine multiple tables, in different sheets, into a new one? - Ask LibreOffice https://ask.libreoffice.org/en/questi...

In one file I will many sheets. I will have one main sheet that summarizes and presents all the data from the other sheets. The other sheets will be raw data. I will not organize or format this data. And I cannot change the structure (such as moving or adding columns). Each of these other sheets will have the same structure (same columns).

However, in my main sheet, I need to be able to aggregate all the data as well as rearrange it. I need the columns in a different order. I need to sort the data. I need additional columns, etc.

To update my data I will need to be able to simply paste new data into the other sheets. The column structure in the other sheets will always be the same. There will be an arbitrary number of rows.

The solution needs to be robust in that the main sheet will continue to function as expected even when I paste new data into the other sheets. And I will continue to enhance the main sheet, so I need to be able to move columns and do other data manipulation operations exactly as I would on any normal sheet.

All my formatting, sorting and column rearranging will be limited to the main sheet.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-12 21:20:19.880379

2 Answers

Sort by » oldest newest most voted
1

answered 2015-12-07 20:09:15 +0100

LKeithJordan gravatar image

updated 2015-12-07 23:54:51 +0100

It seems to me you need more than a spreadsheet approach; you need a database backend with a spreadsheet frontend.

However, if you MUST continue to use a spreadsheet only approach (and depending on the amount of data being maintained), I would suggest you combine all data spreadsheets into one single spreadsheet. That should be no problem, since you indicate they all have the same structure.

If you need to differentiate the data (I assume that's why you have separate sheets), do so with an additional column.

Now you should be able to consolidate the data more easily. Use Pivot Tables, or functions such as VLOOKUP and SUMIF. Depending on your situation, you might also consider using named ranges where appropriate to help with consolidation.

If necessary, use an intermediate sheet for consolidating the data, then use a "final" sheet that references cells in the intermediate sheet for the final results. You can add whatever polish (extra columns, etc.) you need to the final sheet.

=======================
Update 20151207:
Actually, there are a number of resources. Since we are in the Ask LibreOffice forum, let's focus on a LibreOffice solution. First take a look at this link: search results - "connect libreoffice calc to libreoffice base"

The search engine results stemmed from my query: "connect libreoffice calc to libreoffice base" and runs many pages.

Let's take a look at the top link: "How to link a calc spreadsheet to a base database?"

Interestly enough, it is from our own Ask LibreOffice forum. M.A. Riosv's answer contains a link to a tutorial, and the OP (original poster) marked his as the best answer.

I might note however, that the thread is dated 2012 and the article is even older. Still, it may be a good place to start.

Given the age of the article, HSQLDB will be the database engine. This engine uses Java and I personally don't recommend using anything with Java since there are dangerous security holes associated with it. Luckily, the Firebird database engine is now available for LO Data.

My database experience is with FoxPro (a Watcom C derivative) and M$-Access (as part of the M$-Office suite). Among other things, I have linked Excel spreadsheets to Access data tables and stored queries.

I have not yet had time to experiment with LO Base, but as I understand it, you will have to have a security certificate to do any scripting work (whether in LO Calc, LO Base, or elsewhere) -- and this requirement may extend to working with databases. (OTHER FORUM MEMBERS: Please correct me if I am incorrect.)

I don't have it at hand, but I found a link that allows you to obtain a security certificate for free from a FLOSS source. I haven't had time to pursue this issue further. Perhaps you can find the link to which I refer by searching the web.

If you are looking for a more generic learning experience in order to ground ... (more)

edit flag offensive delete link more

Comments

"It seems to me you need more than a spreadsheet approach; you need a database backend with a spreadsheet frontend." -- Yes, that sounds like what I need. Can you point me to some tutorials for that? I studied some SQL in school, but I have no real-world experience with databases and I don't know where to start.

Mountain gravatar imageMountain ( 2015-12-07 20:11:24 +0100 )edit

My answer is too long to include as a comment. Therefore, I will have to modify my original answer.

LKeithJordan gravatar imageLKeithJordan ( 2015-12-07 23:11:40 +0100 )edit

Thank you. I'm starting to read now!

Mountain gravatar imageMountain ( 2015-12-07 23:57:45 +0100 )edit
1

answered 2015-12-07 09:38:18 +0100

pierre-yves samyn gravatar image

updated 2015-12-07 09:40:00 +0100

Hi

If "the column structure in the other sheets will always be the same", you can use 3-D References in formulas like:

=SUM(SEL1.C10:SEL10.C10)

This formula calculates the sum of the C10 cells from sheet SEL1 to sheet SEL10 regardless of the number of intercalated sheets. This video (in French) shows an example...

You can also look at the DataConsolidate (see here) but will be less flexible in terms of presentation.

Regards

edit flag offensive delete link more

Question Tools

2 followers

Stats

Asked: 2015-12-06 23:32:50 +0100

Seen: 13,265 times

Last updated: Dec 07 '15