질문하기

ShroL4pWak's profile - activity

2016-12-29 13:36:29 +0200 받은 배지  조력자 (source)
2016-12-29 10:09:41 +0200 받은 배지  자기 주도 학습자 (source)
2016-12-29 10:09:41 +0200 받은 배지  선생님 (source)
2016-12-29 10:06:33 +0200 받은 배지  유명한 질문 (source)
2016-12-28 15:37:20 +0200 질문에 답변하였습니다 Replicate data in cells on other sheets within same document on a global document basis

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.

2016-12-14 18:03:26 +0200 코멘트달린 답변 Replicate data in cells on other sheets within same document on a global document basis

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.

2016-12-14 17:58:27 +0200 코멘트달린 답변 Replicate data in cells on other sheets within same document on a global document basis

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...

2016-12-14 17:52:16 +0200 코멘트달린 답변 Replicate data in cells on other sheets within same document on a global document basis

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...

2016-12-14 11:38:34 +0200 받은 배지  주목할 만한 질문 (source)
2016-12-07 18:31:00 +0200 코멘트달린 답변 Replicate data in cells on other sheets within same document on a global document basis

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

2016-12-07 18:30:17 +0200 코멘트달린 답변 Replicate data in cells on other sheets within same document on a global document basis

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.

2016-12-07 18:14:19 +0200 받은 배지  학자 (source)
2016-12-06 16:32:01 +0200 받은 배지  인기있는 질문 (source)
2016-12-05 22:59:10 +0200 받은 배지  편집자 (source)
2016-12-05 22:57:32 +0200 질문하였습니다 Replicate data in cells on other sheets within same document on a global document basis

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)