Ask Your Question
0

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

asked 2016-12-05 22:57:32 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 ...
(more)
edit retag flag offensive close merge delete

Comments

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.

Lupp gravatar imageLupp ( 2016-12-06 14:32:32 +0200 )edit

4 Answers

Sort by » oldest newest most voted
1

answered 2016-12-06 16:53:24 +0200

JohnSUN gravatar image

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

edit flag offensive delete link more

Comments

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

ShroL4pWak gravatar imageShroL4pWak ( 2016-12-07 18:31:00 +0200 )edit

Unprotect sheet 'Individually'. Unhide column A. Press Ctrl+F3 - all "strange" formulas is a named ranges in this list.

JohnSUN gravatar imageJohnSUN ( 2016-12-08 09:34:26 +0200 )edit

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 gravatar imageShroL4pWak ( 2016-12-14 17:52:16 +0200 )edit

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 gravatar imageShroL4pWak ( 2016-12-14 17:58:27 +0200 )edit

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 gravatar imageShroL4pWak ( 2016-12-14 18:03:26 +0200 )edit
1

answered 2016-12-06 16:14:10 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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.

ShroL4pWak gravatar imageShroL4pWak ( 2016-12-07 18:30:17 +0200 )edit
1

answered 2016-12-28 15:37:20 +0200

ShroL4pWak gravatar image

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 C:\fakepath\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.

edit flag offensive delete link more

Comments

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!

JohnSUN gravatar imageJohnSUN ( 2016-12-29 10:12:17 +0200 )edit
0

answered 2016-12-06 13:57:51 +0200

Lupp gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-05 22:57:32 +0200

Seen: 326 times

Last updated: Dec 28 '16