Replicate data in cells on other sheets within same document on a global document basis

[ShroL4pWak]

I would need a formula which allows me to replicate the data I insert in the main sheet to each of the sheets identified by a specific name which I input initially. For example, I have a main employee sheet where I keep the records of all employees and then I have a separate sheet for each employee which will replicate the data entered in the general sheet. I need the formula to check whether the name condition is met and afterwards append the data entered in the general sheet to the individual employee sheet in the empty row after the last filled-in row. Thus, I will not be required to copy the row of each employee from the main sheet to the individual sheets. This needs to be done dynamically so I will not be required to enter the formula in each corresponding cell. I would suppose it’s a global document formula, if I’m using the right term. I expect the formula to automatically standby and detect when I input a name included in the condition, and afterwards generate the replication of the data in the right place, pertinent to the name I input.
I assume this would involve a combination of references and conditional formatting.

• Conditional formatting is used in the main employee sheet to determine which name is input and where to replicate it on the individual employee sheets.
• The reference of the main employee sheet is defined in each of the individual employee sheets.

Since I can’t upload an attachment, I am pasting the data from the spreadsheet here as an example.

Main employee sheet

Name	          Age	   Date of birth  Address	            Pay date	Hourly wage	Pay
Sally Grilmers	   35	    11/05/78	  1254 Hickory Dr.	    12/31/2012	15.35	 2410.54
Jacob Miltisworth  28	    05/23/88	  2657 Jensons Ave.	    12/31/2012	14.29	 2105.42
Martin Flemings	   43	    01/11/64	  5342 Landers St.	    12/31/2012	17.58	 2856.41
Wendy Silmerpan    52	    09/15/58	  6684 Filtinger Rd.    12/31/2012	32.58	 5187.36
Shawn Talsyrvith   26	    03/14/91      2384 Miltonaire Ln.   12/31/2012	20.35    3157.31
Input Sally Grilmers for next pay period

Individual employee sheets

Sally Grilmers sheet

Name	        Age	   Date of birth	Address	            Pay date	 Hourly wage	Pay
Sally Grilmers	 35	   11/05/78	        1254 Hickory Dr.    12/31/2012	 15.35	     2410.54
This is an empty row. When I type Sally Grilmers in an empty row in “Main employee sheet”, all data input in “Main employee sheet” automatically gets replicated to its respective column in this sheet.

Jacob Miltisworth sheet

Name	            Age	  Date of birth	 Address	        Pay date	Hourly wage	  Pay
Jacob Miltisworth	28	  05/23/88	     2657 Jensons Ave.	12/31/2012	14.29	    2105.42
This is an empty row. When I type Jacob Miltisworth in an empty row in “Main employee sheet”, all data input in “Main employee sheet” automatically gets replicated to its respective column in this sheet.

Martin Flemings sheet

Name	         Age   Date of birth   Address	         Pay date	 Hourly wage	Pay
Martin Flemings	 43	   01/11/64	       5342 Landers St.	 12/31/2012	 17.58	        2856.41
This is an empty row. When I type Martin Flemings in an empty row in “Main employee sheet”, all data input in “Main employee sheet” automatically gets replicated to its respective column in this sheet.

I have read through all these posts but haven’t found anything relevant to my interest.

https://help.libreoffice.org/Calc/Addresses_and_References,_Absolute_and_Relative

Absolute cell reference shortcut key

Hi! I’m migrating to LibreOffice, from Excel. In Excel, there used to be a feature to flip through all possible absolute cell references while typing, such as from C6 to $C6 to C$6 to $C$6. You could do that by hitting F4. Configuring shortcut keys in LibreOffice, I cannot find a corresponding command. Any hints? Thank you.

https://help.libreoffice.org/Calc/Referencing_a_Cell_in_Another_Document

Calc: how to properly reference cells from other sheet?

Hello everyone, I came across the following difficulty: I copy and “paste special” row from one sheet into some other sheet in the same document. In “paste special” I select the “link” option to reference cells from the original sheet instead of coping values. Cells which have values in the original sheet at the moment when I copy them are referenced properly, however those that are empty are not referenced. Do you possibly know how to make all cells to be referenced, regardless of whether t…

"this cell" in conditional formatting

Hi, How do I use “this cell” in conditional formatting? E.g. I am trying to add a WHOLE column as date (every row is one day, many years in total) and would like to see the weekends. So I’m trying to put conditional formatting for the whole column and use the formula WEEKDAY(;2)>5, but what do I put as cell reference? I want each cell to calculate the formula with its own content, i.e. a date. Thanks ~Harry

(calc) conditional formatting of referenced cells (=$sheet2.$F$16)

is it possible to select (for conditional formatting) cells who refers to cells in other sheets or documents? how? example: =$sheet2.$F$16 but also =$sheet7.$G$10 Thank you

https://ask.libreoffice.org/en/question/39791/copy-conditional-formatting-from-cell-to-cell-in-other-sheet/
https://help.libreoffice.org/Calc/Applying_Conditional_Formatting

Can formulas referring to another sheet be copied to another file with relative addressing?

I have a fairly large (2 meg) spreadsheet that is having severe corruption problems when I try to save it, and once that starts, LibreOffice usually crashes when I try to open it (and cannot “recover” it). I want to take one row of formulas from an earlier, working version and save them to a new sheet. Unfortunately, the existing formulas refer to another sheet ("$MasterList") in the same file. Although I created a sheet called “MasterList” in a new, blank file, when I attempt to copy the formu…

I greatly appreciate your help.

[Lupp]

By the way: ‘Age’ is redundant with respect to ‘DateOfBirth’ assuming it should be the actual age today. It’s a well proven rule of data keeping to not include redundant values with data. How to make sure the needed updates? In the given table the ages are wrong as calculated from today (2016-12-06) by:
-3 years, (correct), -9 years, -6 years, -1 year.
If the values are supposed to be ‘AgeAtStartOfCurrentEmployment’, this should be expressed by the header.

[mariosv]

If I understand your question, one way it’s having a pivot table for every employ that takes their data from the main table.
Pivot tables need to be updated, there is an useful macro to update all at once on refresh_DBRanges_And_Pilots

Sample file with pivot tables.
ExtractData_PivotTable.ods

the button runs the macro, Menu/Tools/Options/Security/Macros must be medium or low to run the macro.
Source data on pivot tables also must be enough to catch new rows.

[ShroL4pWak]

This reply resolves my issue very comprehensively and thoroughly. Your post offers a prompt, practical, and explicit solution. I would have given you a +100 but I can’t vote yet.

[JohnSUN]

Just a small demonstration of what said esteemed colleague @Lupp - Replicate data.ods

[ShroL4pWak]

Your example outputs the desired result. But may I please know how it’s done? Thanks.

[JohnSUN]

Unprotect sheet ‘Individually’. Unhide column A. Press Ctrl+F3 - all “strange” formulas is a named ranges in this list.

[ShroL4pWak]

Alright, all went well so far. I have adapted the formula expressions and names to my document but I want to extend the array range. I’m adding more columns to the Main sheet and inputting relevant data in the row of each person. I highlighted the whole array range and extended it with the cross that shows up when I hover over the black square handle of the selected cell. Continued in next comment…

[ShroL4pWak]

Let’s say I create four new columns in the main page with the employees’ phone, position, college degree, years of work in the company. How do I replicate these columns in the Individual sheet as well? Once I extended the {=getNextRow} array to the next 4 columns in the Individual sheet, it just starts all over from the first column: Age, Date of birth, Address, Pay date. Continued in next comment…

[ShroL4pWak]

I tried messing around with the getNextRow formula expression and changed the last digit to 10 instead of 6, where it ends in (Filters,$C1),1,1,6)),"") but it doesn’t give me the desired result. Is there something I’m missing? Or should I add a formula? If I change 6 to 10 it will highlight cells included in the array range all the way up to ten cells, instead of just six as done previously. But it will not continue the columns in the Main sheet. May I please have more advice. Thanks.

[ShroL4pWak]

Formula expression names must be defined or existing ones managed

1. Unlock “Individually” sheet in document attached above by JohnSUN
2. Navigate on main menu to: Insert > Names > Manage (Ctrl+F3)
3. Input data according to attached document and adapt it to your needs
   Example_Define_names_and_formulas_in_LibreOffice_Calc.ods

All information so far is credited to JohnSUN.

In order to extend the array range and display more rows from the referenced “Main” sheet in the “Individually” sheet where the formula expression names reside, you must:

1. highlight each row on “Individually” sheet and set it as an array range
2. Ctrl+F2 to open Function Wizard (icon located as 2nd item in Formula Bar)
3. Structure tab (Shift+Tab)
4. check Array option (Alt+A)
5. input array formula or formula expression name in Formula field (Shift+Tab to navigate from Array option to Formula field)
6. click OK (Enter key)
7. The row fills up with whatever is on the referenced sheet
8. May create a macro to accomplish this operation
9. Any new document created may be in either Open document spreadsheet .ods format or .xls format for the formulas to work.

Trying to highlight more than one row will not replicate the data correctly.
Copying the sheet to another document might cause errors even if the same formula expressions are used and the reference name is adapted to the new document. The whole array range must be deleted and recreated row by row. A macro may be recorded în order to automatically accomplish this repetitive task.

Array range extension in formula expression

In order to extend the range of the output data replicated from the reference, the getNextRow formula expression must be edited from the last digit which is “6” in this case: (Filters,$C1),1,1,6)),"") . If there are six columns in the main sheet and more than six in the replicated sheet, only the first six columns will be replicated. If more columns are added to the main sheet, the same number of columns will be filled-in with data in the replicated sheet as well, once the array range is extended by modifying the digit in the formula expression. For example, I change (Filters,$C1),1,1,6)),"") to (Filters,$C1),1,1,25)),"") and all 25 columns, or fields, will be replicated from the “Main” sheet to the “Individually” sheet.

[JohnSUN]

Excellent work, my friend! You yourself understood quite difficult trick and tell people how to do it. Congratulations, it’s really done well. Happy New Year!

[Lupp]

This isn’t how spreadsheets work. You want to push data based on the content of a cell or on the recent input anywhere in a range. The Spreadsheet-Do is to design formulae calling data based on references, probably processing them in a specific way based on standard functions, and then returning the result to exactly the cell in which the fomula is contained.

Regarding some restrictions user code might do what you want. This is outside of the range where answers can describe how to do it. It would need to suggest an explicit solution if not just saying “use the api”. It will also be error-prone and simply should be regarded “bad practise”.

Moreover it should be regarded bad practise to create many sheets, each repeating just the information contained in one specific set of data or just containing selected dataset entered there. It is also limited to a maximum number of sheets (presently 256) a Calc document can contain. You might create one sheet capable of showing a specific dataset (in a specific order/formatting) selected from your main sheet by entering a name (e.g. or some other selector) into a dedicated cell. The same sheet will show a different set as soon as you edit the selector to a different content.