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

Question

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 Total 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    Total 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   Total 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    Total 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 many posts but haven’t found anything relevant to my interest.

I greatly appreciate your help.

Answer 1

Formula expression names must be defined or existing ones managed.

1. Navigate on main menu to: Insert > Names > Define
2. Navigate on main menu to: Insert > Names > Manage (Ctrl+F3)
3. adapt according to following scheme

Define names

Name            Range or formula expression                             Scope  
Filters         $A$1:$A$2048                                            Document (Global)
FullData        $Main.$A$1:$G$2048                                       Document (Global)
getNextRow      IFERROR(IF(COUNT(Filters)>=N($C1),OFFSET                 Document (Global)
                ($Main.$A$1,SMALL(Filters,$C1),1,1,6)),"")
Names           OFFSET(FullData ,1,0,COUNTA(OFFSET                      Document (Global)
                (FullData ,1,0,ROWS(FullData),1)),1)
NumberOfResult  IF(COUNT(Filters)>=(ROW()-ROW                           Document (Global)
                (StartResultTable)),ROW()-ROW(StartResultTable),"")
RowNumber       IF(SelectedName="","", IF($Main.$A1=SelectedName,         Document (Global)
                ROW()-1,""))                                            Document (Global)
SelectedName    $Individually.$E$1                                     Document (Global)
StartResultTable    $Individually.$C$5                                 Document (Global)

Each row on Individually sheet must be highlighted and set as an array range.

1. Ctrl+F2 to open Function Wizard (icon located as 2nd item in Formula Bar)
2. Structure tab (Shift+Tab)
3. check Array option (Alt+A)
4. input array formula or formula expression name in Formula field (Shift+Tab to navigate from Array option to Formula field)
5. click OK (Enter key)
6. The row fills up with whatever is on the referenced sheet
7. May create a macro to accomplish this operation
8. 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.