If it helps then so be it - if it doesn’t then feel free to castigate me.
I regularly search the internet for solutions in LO and invariably end up on this ASKLO site.
From experience, I learned that it’s a good idea to create a LO document “Tips&Tricks” and, utilising the LO function “insert Hyperlink”, I then have a nicely coordinated/indexed document containing all the help I need for my current requirements - together with many topics I suspect I will need in the future.
The function also allows me to embed links to my “Tips&Tricks” document in any new document where I think an “Aide Memoire” would be beneficial.
Samples of the code and the links can be incorporated into the document so it’s a bit of an improvement on HEY SIRI, HEY CORTANA & HEY GOOGLE.
And which tips seems most valuable for you? Which application do you use frequently, Writer, Calc, Impress, Draw? Do you feel all applications have the same degree of help here? Have you any suggestion on how we could improve our answers?
Do you think AskLO offers full assistance equally to newbies, regular, advanced users and perhaps experts on a pinpoint issue?
In short, is it worth we spend so much time on it?
I tend to be more interested in the CALC solutions so I couldn’t really comment on the value of help for the other units.
I quite often find that some of the respondents - who obviously have a great working knowledge of the product - offer quite technical explanations which may be beyond the ken of the users seeking simple solutions to their own problems - not “in your face” demonstrations of the superior knowledge of the respondents.
How easy is it for an experienced user to identify the skill and comprehension level of the individual seeking assistance? Do the respondents tend to assume the enquirer has a greater technical competence than the reality? It’s an impossible scenario - sometimes it “clicks” immediately and other times it leaves the enquirer somewhat bemused - and feeling as though they are being denigrated.
Always. Most FOSS users don’t have the resources to engage professional support -as in paid for, not quality of commitment - so, if they were to be abandoned by the creators and experienced users then they would simply abandon the products.
Everyone who does this gradually collects a library of macros and other finds.
Knowledge can be transferred, but experience must be acquired independently. And it is experience that is important.
By the way, I often search for solutions on MS Excel. There is more choice. And I use the received information in relation to LO Calc. But to do this, you need to know both products well.
Macros are evil. Don’t waste your time with that.
Use Templates & Styles, databases instead of spreadsheets.
Writer: Templates & Styles.
Impress: Templates & Styles.
Draw: Templates & Styles.
Databases actually do what most people try to do with spreadsheets.
If you want to raise your level, study Mike Girvin’s book*. Somewhere posted for free on the resources of the author himself. But this is only about formulas. Macros are another topic. Application development is an even more complex topic, because it combines everything together.
Spreadsheets are clearer. Allow you to start working immediately (data entry). Databases require more costs at the start: tables, queries, forms, reports need to be created. Data normalization, relationships, cardinality, referential integrity, keys and indexes, deletion anomalies, etc. SQL. Databases require more qualifications from the user.
But it is not always necessary to fire a cannon at sparrows. Where there is no need to accumulate large amounts of data, but there is a flat table (2D), it is easier to analyze it using spreadsheets. And there is also a lot of such data.
Macros for deleting duplicate entries, removing extra spaces in one fell swoop - you can’t do without them. Because the spreadsheet accepts everything, and the data often gets dirty. And the database just won’t let you do it (shit the data).
*In Excel SUMPRODUCT ignores logical values, that is, it treats them as 0. In Calc TRUE is 1 and FALSE is 0 (but again, this is knowledge, and you will miss it, and experience is important). All the other stuff is similar.
I think the biggest obstacle for database use is that the user rarely appreciates the possibilities for a database when first embarking on the project.
What initially presents as a simple 2D structure is produced fairly quickly and with simple techniques in a spreadsheet.
The user then realises the potential for a few “bells & whistles” which are easily added and ultimately ends up with a multi-sheet hybrid resembling an untidy database. It’s so easy to retrospectively add supplementary lookup tables and sheets or even another file that we don’t even realise that we’re cramming a database functionality onto a spreadsheet.
By that stage it’s inevitably too late to convert it to a pure database as the flexibility of a spreadsheet has already allowed the user to develop a solution with easily applied formulae and functions that are far more complex to achieve in a database equivalent. A daunting prospect for a noobie with a few personal pet projects.
The opposite is true. Spreadsheets are plastic toys. Shallow learning curve, no power. Formulas become extremely error prone and unmanagable. You (or your Basic code) needs to manage all the references as you add new records.
Untrained end users can not work with spreadsheets. They enter text in numeric fields, numbers in text fields, copy around cells destroying your elaborated validation and conditional formatting, append data without expanding references, delete values by hitting the space key. The formula language has more idiosyncrasies than clear rules. Keeping all the data constantly in memory, the whole thing is a house of cards when things go wrong.
There is not a single user-friendly solution based on spreadsheets. Why can’t we find a single invoicing or book keeping solution on sheets? Since 30 years people try to re-invent the wheel on sheets while every professional solution that realy works is founded on some kind of database.
The database lies somewhere deeper, the spreadsheet is closer, on the surface. @WhiteKnight is right in many ways. In the world of Microsoft Office, users are actively and everywhere taught to work in MS Excel, and no one teaches to work in MS Access.
Although I think everything is complicated, but databases are good for established solutions. I have been working with the MS Access database for a long time, but there have been no tasks for this for a long time. With a competent approach, I emphasize, spreadsheets allow me to achieve results faster.
Recently I learned that MS Excel can mimic relations with primary keys and foreign key. I have never seen a single question why Calc can’t do that. Spreadsheet users never think of relations. It is a completely foreign land to them.
I agree. Every month I simply access my internet bank and download an excel file with that month’s transactions and copy it into my own spreadsheet.
I have a single table where a simple descriptor is added to identify the type of expenditure. Additional columns split the dates into components so I can simply filter the data to provide me with a list of all transactions within 2 days of my birthday in a target year or all years but only if they were debits exceeding a certain value. Or, I remember buying that item specifically on a particular bank holidiay but which year was it? Where did I buy it? How much? filter transactions for May 1st for the last five years. Worst case scenario is that I have to peruse 10 items. Why would I need to write a database to do that. I can tell my bank manager what my average balance was on the 13th July every year for the last 20 years and he charges me for banking😆
@Villeroy: Yes, I agree. There are no relationships. Keys too.
I would be tired of repeating: 1) the data on the field should be atomic, that is, indivisible; 2) there should be no repeating groups (arrays, 1st form of data normalization); 3) the data should be stored once in one place. These are all typical mistakes of beginners. Point 3 is more complicated. Data redundancy generates a lot of errors. It is necessary to periodically put the data in order, as a rule, before analyzing the data, macros are needed for this. By the way, I don’t always use pivot tables (they are very weak in LO Calc), but I always create them for cross-checking data.
Two macros are enough for me: to delete duplicate entries, remove extra spaces.
E.g., a data table with two inputs (Multiple Operations) displays information in all details. And there is a “Total” field for rows/columns of this table (SUM). And in the cell next to it there is an array formula or a regular one that calculates the entire database. Two cells are compared. If there is a mismatch, the error information is output.
I disagree. I have arrays where the table is actually 3 overlapping LOOKUP() arrays so I can access a primary key on column 1 which provides a value from column 3 and then view the same table using that key in say column 2 for either a value or another key to another table on another sheet in column 4. Often, the data in those tables is the result of an embedded formula and it was simpler to perceive than creating a database with three tables each maintaining it’s own key.
In terms of our discussion, the main advantage of databases is that they allow you to properly organize data entry. And the main task of a user working with a spreadsheet is to enter the data correctly.
Spreadsheets give you more freedom to work. The main thing is to use this freedom correctly. And that’s the advantage, and that’s the whole trouble.
But let’s not discount spreadsheets. They have their own niche.
I would agree but as the only user of whatever I create, I don’t have to worry so much about other people making mistakes or ME misunderstanding what they asked for. Also, it comes quite naturally to me to NOT repeat data. I simply refer to it whether it’s on the same sheet, another sheet or indeed in another file. Hyperlinking is one of the gems I really enjoy.
Did MS buy FOXPRO and turn it into ACCESS or did they just buy and suppress it so they had no competitor for ACCESS?
They are different RDBMS (foxpro is more powerful).
Well no. Data redundancy is an inevitability. The data must be in records (tuples). And if we do not create a table for each entity, and do not establish relations between them, then data redundancy is the fee for storing data in one flat table. And this is the source of errors. Here is the answer to the question why users don’t use pivot tables. They don’t want to store their data in records with all the redundant information.
@Villeroy is right here. It is necessary to take a ruler and beat users on the hands. 
Is it wrong of me to consider a “label” in the row as NOT data?
One of my structures is repeating 8 events each day but those events are fixed. I could just as easily set them in a VLOOKUP() with the key 1 - 8 (I didn’t originally because I hadn’t assimilated LOOKUP() until later) and the labels don’t really perform any function other than remind me that the first meal of the day is called “breakfast”.
Please don’t give him a ruler - he’s so passionate about DBs that he’d probably mistake my head for my hands.
I do the same with a database. 110,000 seamless records from 5 bank accounts over 12 years. Whenever I need to calculate anything in a spreadsheet (analysis, what-if-scenarios, interest rates, critical values), I can access any subset of those 190,000 records effortless. No more =IF(A2="";…) because there can not be any blank value. No more =IFERROR(A2="";…) because there can not be any errors. No more =IFNA(VLOOKUP(…) because referencial integrity is granted.
I would never ever again store more than 1000 flat records in on sheet.