Group sheets by type

[Robitron]

I'm setting up a double entry account spreadsheet for my family's finances. As such, I have multiple checking/savings and wallet accounts, expense accounts, equity accounts and financial report sheets.
I would like to be able to group the sheets together into categories (such as Checking Sheets, Savings Sheets, Expenses, and so on) so that when recording expenses, I can quickly open just the expense sheets, checking accounts and the General Journal and hide all other groups to make switching between individual sheets less be so daunting. I know I can do so by creating macros to search for specific sheets and hide all others, but I'd rather just click on several sheets and group them together to quickly hide each group and show only relevant groups.

Is this possible?

[Hagar Delest]

There was an old discussion about that but I guess that the answer is just no: Can i combine multiple sheets into groups?

[Robitron]

Okay, thanks. That's disappointing but I guess I'll just create macros.

[RoryOF]

A better approach might be to use a database, with customised reports to give a pseudo-spreadsheet appearance.

When I needed such an accounting system (years ago) , rather than reinvent the wheel, I used a commercial application, an early version of QuickBooks.

[Villeroy]

Each and every accounting software is based on some kind of database. Spreadsheets can't handle row sets nor transactions.

[Villeroy]

Attached is a simple database demo.
There are only 3 simple tables taking the accounts, transactions and bookings.
Queries are analog to formulas. Unlike a formula, a query returns a whole record set of columns and rows.
The user interface (what a user is supposed to work with) consists of forms and reports.
The accounts form takes names and descriptions of accounts. The number is automatically set, which may not be what you want in reality.
The transactions form takes a name and description of a transaction, sets an automatic time stamp and you can add bookings on the debit and credit side.
There is a report which shows the transactions grouped by their accounts. Reports are for printing.
Table data and queries can be used in spreadsheets.

[Lupp]

Having in mind that grouping of sheets and a feature allowing to hide all sheets not belonging to a specific group may not only be of interest if spreadsheets are (opinion dependent) misused in one or another way, I ignored the use-case described in the original post, and reduced the question to the short version of the subject "Group sheets by type".

Since sheet groups aren't provided in a predefined way, I assumed a very simple case where a partition of all the sheets of a Calc document is automatically created based on the first two characters of the sheet names.

Now I could write two extremely simple subroutines, one for hiding all the sheets not belonging to the group of the active sheet, and one to show all sheets again.

My example is made with LibreOffice where for a long time now also context menus can be customized. I used the feature for the sheet tabs.
Working with AOO the routines must be called in a different way. One may be the insertion of custom items into the View menu. A (possibly floating) custom toolbar may also provide a good solution.

aoo111504sheetGrouping.ods

(11.52 KiB) Downloaded 12 times

=== Editing 2024-05-07 10:31 UTC ===
Since there was some interest in this post and the supplied example, I reworked it slightly to also implement the second (View menu) and third (extra toolbar) mentioned way to access the included user code. For me both ways work also under AOO 4.1.7. See new attachment. I also tested the feature concerning trusted file locations anew with this example under LibO and under AOO as well.