I am going to need several different reports and would like to find a way to create a master and have the linked sheets updated when there is a change to the master. I have been copying the information from the master to the sheet for the report, is it possible to link the data and have the report sheets update from the master if there are additions or deletions of lines. For example, if I have lines 1,2,3,4 in the master and sheet 1, then I delete line 2 from the master, can that line be deleted in the report sheet? Any help would be appreciated.
I didn’t want to over complicate this project. There are 72 people with one definite guest and a possible 2 additional guests. I don’t want to do the work to create a database (it is a bit beyond my capabilities and I could spend the time, but I’m not getting paid). The project will be over at the beginning of February. I am just going to copy the data into another sheet within the spreadsheet for each different report and generate the reports from the Individual sheet. Thanks for looking at this and making the suggestions.
This is how you are going to do, but not a solution.
Abstain from cross links between spreadsheets. It is a major source of trouble. Simply put all related data in one spreadsheet document.
Abstain from any kind of spreadsheet. A spreadsheet is a very poor database surrogate with too many pitfalls, references lost, persistent need to copy around data and formulas.
Solution #3 (for the poor database surrogate)
- Apply solution #1. Copy all involved sheets into the same document.
2a) Connect to existing db of type “Spreadheet”
2b) Specify your spreadsheet holding the pseudo- database.
3c) Yes, register the database for cross-document connections.
3d) Save the database document, close it and forget it for now.
- Open the same spreadsheet or any other.
- Open the data source window [Ctrl+Shift+F4]
There you find your registered data source and all the used areas of every sheet as tables of a pseudo-database. These table entries refer to the used sheet areas. When you append new data, even without cell insertion, the database includes the new data after you saved the spreadsheet.
- Get a table icon and drag it to some spreadsheet cell. This will import a linked database range.
- Call dialog menu:Data>Define…, select your database range named like “Import1” and drill down the extra options. Check “keep formatting” and check “insert/remove cells”.
Now you can call Data>Refresh while the cell cursor is anywhere within the import range and you get a new copy of the referenced data reflecting the used area of the spreadsheet that constitutes a table of your pseudo-database. No adjacent data will be overwritten because new rows will be inserted for new records. You can format the cells any way you want because only the raw data are linked to the database.
P.S. Solution #3 works with multiple spreadsheet documents almost as well if you register them as multiple pseudo-databases. You can link many databases from various sources (including true databases) in the same spreadsheet document.
Presuming you are using LibreBase:Reports, and have data in a Base file, then try : 1) create the Master as a Table and other associated ‘tables’ as Views 2) create text-based Reports for those different Views based on the Master, … or … 3) instead of 2), create spread-sheet based Reports, then use a macro to export each Report to a CSV file , 4)then link each CSV file to a sheet on a ‘final’ spreadsheet
PS: actually, if your .odb (Base) file is Registered (see:Base>tools>options>database>register database) can just link the different Views and Master to different sheets on a single .ods using the Data-sources menu…