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 yourself in databases, I suggest expanding your search to include the MS-Office Professional suite. Look for topics on M$-Access, and later, on interfacing Excel and Access.
Take a look at Udemy.com, but be aware that their resources may include both older and newer courses, so make sure you get what you need. Lynda.com may be another resource (recently acquired by LinkedIn). Both of these resources are PAID resources.
I did a search for “free database courses online” and here is a link to the search engine results:
search results - “free database courses online”
Notice the wide range of resources listed in the search results.
Hopefully, some of these suggestions will help. I strongly urge you keep at it. What you learn will be invaluable.
Best of luck.
=======================
PLEASE CLICK THE CHECK MARK NEXT TO MY RESPONSE if you believe it to be the best answer to your question.
