[Solved] Indexed spreadsheet with 2-dimensional sheet tab grid

[onepound]

At the moment I have a simple .ods file with sheets laid out as follows:

calc.JPG (19.1 KiB) Viewed 1547 times

for a given year. And another file for the next year etcetera.

What I would like to be able to have multiple years accessible and editable preferably in the same file laid out with the same sheet arrangement but indexed by year. So it would be like an array so I can switch between years at will each set looking exactly like in the picture and share data between the years like one does between sheets.

I hope that makes sense, it is difficult to explain.

[Villeroy]

What you want to do is one of the most popular misconceptions. Put everything in one list and let the software do the work.

https://forum.openoffice.org/en/forum/d ... p?id=46190

[onepound]

Put everything in one list and let the software do the work.

https://forum.openoffice.org/en/forum/d ... p?id=46190

if I have understood this correctly then no.

What I want is those set of sheets shown in the picture across the bottom forming one dimension of an array and a second dimension in this case given by the year. Off course this multidimensional organisation of sheets might not be within the capability of the software though there is mention of database access capability.

There is a clumsy solution which would be to prescript those sheets like 2022_EOY so that they are all in the same file but then I would have only have one dimension of sheets sprawled across.

Alternatively a solution is suggested here https://forum.openoffice.org/en/forum/v ... other+file
which uses multiple files but allows one to reference cells between files. This solution will do though it would have been nice if one could have done what I was proposing.

[Lupp]

I hope that makes sense, it is difficult to explain.

Concerning the sense there are doubts as @Villeroy already explained.
If you filter the data from a list across the years, or from a database, you can easily prepare a sheet for prettyprint and/or additional evaluations, and select the year (or other range of time / other filter criteria) for which the contents need to be pulled from the source into that specialized sheet. If you hard-cut the original data into subsets using many sheets, each one of these sheets need to contain/support all the formatting, formula apparatus, whatever ... basically only needed for the one final sheet.

If you feel you weren't correctly understood, you should create a simplified rexample, but not too much simplified. It must show clearly "what I have" and "what I want. Images are of no use in such a case. If your concept only is present in your image-world it may not be suitable for reasonable IT. In any case something "difficult to explain" is mainly a task for the questioner who needs to accept the difficulty.

[onepound]

Concerning the sense there are doubts as @Villeroy already explained.

I don't think he explained anything - he was just plain insulting:

"What you want to do is one of the most popular misconceptions. Put everything in one list and let the software do the work."

[Lupp]

...
If you feel you weren't correctly understood, you should create a simplified example, but not too much simplified. It must show clearly "what I have" and "what I want. Images are of no use in such a case. If your concept only is present in your image-world it may not be suitable for reasonable IT. In any case something "difficult to explain" is mainly a task for the questioner who needs to accept the difficulty.

That' the substance of what I posted above.

I won't comment on the wording by a different contributor to whom I only pointed concerning my (and his) doubts about the design of that package of sheets. I am very pragmatic concerning raw sketches we use spreadsheets for - and which we probably trash the next day. Long-term solutions for serious tasks should be thoroughly designed, and from my experience I wouldn't excpect a multi-sheet design to be long-term-proof.

With different words: If I have a few hundred sets of data, whatever I do must only be reliably correct. Beyond this need it may be done allowing for some sloppiness. To take 50 h for the design, and to refine it then taking another 30 h may not leave enough time in the end to accomplish the actual task at all.

Long-term solutions need long-term maintenance and probably long-term documentation to ensure long-term usability of data.

Errors expected. 25 years ago I had serious problems with a database that was developed by a professional without a realistc view on its long-term size and by that on its long-term stability and usability. It's not the means that solves problems. It's the users of those means - even if that users may one day be AI. But a very relevant step to any solution is the choice of appropriate means. It's no sense in asking you how to fly to Strasbourg on my bicycle.

[Mountaineer]

... forming one dimension of an array and a second dimension in this case given by the year. Off course this multidimensional organisation of sheets might not be within the capability of the software though there is mention of database access capability.

Dimensions are a bit more complicated: A sheet is av2-dimensional grid, so a pile of sheets is 3-dimensions and - similiar to some ideas in physics - your year would be the 4.th dimension.

Today most databases are designed around relations and for historic reason tend to create a model without double data, so often "atomize" rows, but i guess it is not impossible to create a model for that.
But as you didn't mention much calculation between your years you actually only need a quick file-switcher for templates filled for every year. (But my guess the request calculations will come later).

...
which uses multiple files but allows one to reference cells between files. This solution will do

You should be aware calls to external files are much slower than internal references. (But now nobody needs to change floppy disks anymore for this, so there is progress) Also external files may not be available, so need another layer of security.

though it would have been nice if one could have done what I was proposing.

It is free software in a free world. Start coding your hypertorus of data. Don't ask for help, or you will meet a lot of people like Villeroy suggesting you to use databases.

PS: I agree with Villeroy. The software we use is very efficient in filtering data from tables for presentation. To work the other way round is like the difference between screw and nail - only one of them work nicely with a hammer. So I'm also one of this guys who say: put all in one table and use filters, pivots, querys, database-views, partition-functions to get access. But there are other ways. And If your top priority is to have your table repeated... good luck with this.

[keme]

Concerning the sense there are doubts as @Villeroy already explained.

I don't think he explained anything - he was just plain insulting:

"What you want to do is one of the most popular misconceptions. Put everything in one list and let the software do the work."

Villeroy does have an "economical" form of expression, often terse and easily taken as offensive. In my experience, only in very rare cases does he intend an offense.

His response to you is not an insult. He is telling you two things:

• You are not the first to make the assumption. It is a very common mistake.
• "All in one list" is a better solution

It is a better solution in ~99% of the cases. In most of the remaining ~1% it is just as good as the alternative(s).
In databases(*), the "multiple dimensions" is implemented by field values; in the sample sheet Villeroy supplied, the date/category/person fields constitute three logical dimensions. Other logical dimensions like cost location, VAT classification, etc. can be added at will. Just insert a new column.

Note that while having everything in one sheet (as in Villeroy's sample file) helps with visualizing the interconnected nature of the elements, it is a mess to work with. In an actual worksheet file, the data table should be alone in one sheet, and the different extracts on their own sheets.

(*) Yes, I said "Database". The dataset constitutes a database, whether or not you use the designated Base tool, and/or properly normalize the data structure.

[Hagar Delest]

+1 with keme.

You seem to have a "visual" conception of a database, like in a movie: the user would have a visual interface with a "layer" of tabs for each year and he can browse the stacked layers to select a year and access the bunch of tabs of that year. Nice but what is the point? Why do you need the data for each year to be separated?

A database is based on huge table in which the data are stored. Then the added value is all that goes around to manipulate the data. But at its core, there is a single table, even if it can mimic all kinds of presentation afterward.

What you could do to have your stacked up sheets is to create a "master" spreadsheet that lists (with hyperlinks) in each row either the mere file name or each tab (in each cell) of each file for each year. That way, you have a kind of index and click the appropriate cell to go to the relevant tab in the relevant year.

But then, if later you need to split travels in domestic and abroad for example, you'll have to modify all the files.
What is wrong with the one list for all proposed in this topic? Have you checked the example file from Villeroy?

[onepound]

First of all thank you for your contributions.

For now this is my solution as I mentioned before:

Suggested here https://forum.openoffice.org/en/forum/v ... other+file

This uses multiple files but allows one to reference cells between files.

Therefore I can have my simple separate files for each year and access the data I need that is passed on from each successive year by referencing the cell in the link to an external file as described in the post "Copy cell value from a spreadsheet into another".