Strategy advice sought, managing a 8 sheet ods

[Villeroy]

There should be no other sheets where to enter data. Insert rows anywhere into my source list, add a new record and right-click>Refresh the pivot tables (this could be automatized).
Now imagine that a predecessor of that data table was split into one sheet per person or one sheet per product or one sheet per month. It would be very difficult to aggregate and compare all the tables.
With one table, this is very easy. Pivot tables are just one option among others.
Unlike humans, computer software can easily deal with thousands of rows at once.

• My data table does not require any sorting.
  – You can insert data anywhere you want.
  – You can sort the table any way you need right now.
  – The aggregation works, no matter where you insert rows, how the table may be sorted or filtered.
  – No need to look up and select the right sheet for your data input.
• Use the auto-filter buttons in the header row in order to get a filtered view of one particular person or product as in your single sheets.

Just one thing: "Tools>Options>Calc>General>“Expand references …”
This option should be checked, so references will expand even if you insert new records directly below the list.

[clear]

@Villeroy
Initially, I misunderstood your first post. Having had no knowledge of pivot tables, I thought you were saying that a pivot table would allow me to merge the 8 sheets.
.
At this point I’ve merged the 8 sheets into one combined sheet, doing so by copying a sheet, then Paste Special > Paste Special > Options > As Links. That is working fine, except that it is not creating links for cells that are empty in the source sheet. The box Options > ignore empty cells is not checked. And I haven’t tried the copy/paste with the ignore box checked. The other draw-back for doing it this way is that any new rows in a source sheet will also need to copy/pasted into the combined sheet.

[Villeroy]

@clear

At this point I’ve merged the 8 sheets into one combined sheet, doing so by copying a sheet, then Paste Special > Paste Special > Options > As Links.

Do not link to the 8 sheets. Copy all Data into one list, then remove the 8 sheets.
Having your data split into 8 implies some grouping (one group per sheet). You should add an additional column specifying the group. If some records used to be on a sheet named “David”, the respective records should have an additional entry “David” in an extra column. You would lose information, otherwise.
Imagine that my list used to be split 7 sheets, one sheet for each person, each sheet named after the person. I would have needed to preserve the person name in the merged table’s column “Person”.

[clear]

@Villeroy

Do not link to the 8 sheets. Copy all Data into one list, then remove the 8 sheets.

.
I may do just that. At my novice level with LO, it just seems scary to have all the data vulnerable to an errant keystroke.
.
Is there a way to write-protect all the cells, and then be able to toggle the write protection on a cell by cell basis ?
.
I am still making two faux pas. When editing directly in a cell and the cursor is in the middle of the text, I’ll hit the Home (End) key to move the cursor to the beginning (end) of the text. Also, when finished editing a formula directly in a cell, I’ll click outside the cell (which ends up being a click in some random cell) to exit the edit.

[Villeroy]

A spreadsheet is an “intuitive” tool with a maximum of versatility. It can’t be foolproof.
If it has to be more foolproof, forget the entire spreadsheet and use a database instead. A database has a very steep learning curve but once built-up, it is very easy to use.

[clear]

I used databases at work way back in the mid 80’s. Our huge 40MB external drive was locked up every night. At that time my girlfriend was a technical writer on the dBASE III team at Ashton Tate. She was excited to upgrade her personal Kaypro to the newly released Macintosh. Some 10 years ago, I worked through a couple of Coursera’s on SQL, but never put it to use, so those skills are gone.

[Villeroy]

Well, then use the arithmetic calculator as a database surrogate, but don’t expect too many database features (such as input forms).

[clear]

would have been quicker !

[Villeroy]

You asked for a strategy how to do that on sheet. The strategy is not to split equally structured data and use the tools provided by your software. Same as any database developer would do.

[clear]

Your suggestion to put all data in one sheet makes perfect sense. I agree with it.
.
I need to figure out how to safe guard the all-in-one sheet from errant key strokes, yet still allow single cells to be toggled in and out of editability.
.
In the meantime, when source sheets have empty cells, this has become my strategy for creating a combined sheet using links. It’s a 10-step program:
.

1. Select the tab of the source sheet, note the number of rows
2. Copy a single data line that has values in every cell
3. Switch to the Combined sheet
4. Paste into the combined sheet Special > Paste Special > Options > As Links
5. Checkmark View > Show Formulas
6. Remove the row dollar sign: find \$([0-9]*)$ … replace $1
7. Drag the row downward and upward to match the source sheet
8. Replace the row dollar sign: ([0-9]*)$ … replace \$$1
9. Remove checkmark from View > Show Formulas
10. Repeat for the other sheets

[robleyd]

@clear

all the data vulnerable to an errant keystroke.

You of course have implemented some sort of backup process to help safeguard your valuable data? You may consider installing the Timestamped backup extension. You might also wish to read Preventing data disaster.

[clear]

Tell me how to detect an errant keystroke that modifies a cell ?

[CRDF]

@clear

Tell me how to detect an errant keystroke that modifies a cell?

What inserting errors have to do with managing your 8 sheets

[clear]

Do you have a way to detect “insertion errors” ?

[CRDF]

Of course not.
Because of this I’m unable to grasp your point.
You were advised to consolidate your 8 sheets in 1.
What this has to do with “errand keystrokes”

[clear]

Maybe one of these:

• LO may track mod-time for each cell
• Duplicate the master sheet when opening the ods, then in a third sheet diff the two. Upon closing (or from time-to-time) see what has changed.
• some other SOP

[robleyd]

This is drifting off topic, but perhaps Recording Changes ?

Of course Calc is unable to determine the difference between intended and accidental data entry.

[Wanderer]

@clear

I need to figure out how to safe guard the all-in-one sheet from errant key strokes, yet still allow single cells to be toggled in and out of editability.

Read about

• data validity in Calc
• protecting cells/sheets

If you run into trouble/ need help open a another topic for this problem.
That seems to be this one:

CALC: How to detect "insertion errors" aka, "errant keystrokes" affecting single cells

I’m a relatively new LO user. What is the SOP for detecting errant keystrokes that modify a single cell? . For example a check box analogous to Show Formulas, for Show Modification Date-Time, and the facility to highlight cells with a timestamp more recent than X time ago.

[clear]

As this thread seems to have run its course …

In summary, there was only one suggestion, convert to an all-in-one sheet by @Villeroy (many thanks). Though the suggestion is prudent advice to consider going forward, it is evident I was inarticulate. So I’ve created a more explicit thread

Calc: ? features to simplify analyses across multiple sheets / files?

Perhaps Calc has a concatenation feature to define a ‘virtual’ sheet. For example virtual1 = sheet4 + sheet7 + file2.sheet5. Perhaps Calc has a feature analogous Writer’s ability to inserting a table derived from an ods file. For example Import Range in analogy to Insert Row. Perhaps Calc has a feature to define a virtual column. For example virtual1.A = sheet8.G + sheet3.G1:G36 + sheet5.D7:D128 + file3.sheet1.D … with analyses being performed on virtual1.A Being relatively new to spreadsheet…