LibreOffice Calc: How to transfer Column from Sheet 1 to Sheet 2 while adding empty Row between each Row of Sheet 1?

[Pantona]

We have a table consisting of arbitrary dates in column A (sheet 1). Those dates will define measurement days when coworkers pick up samples to analyze them. The identified measurement values themselves will be written down in sheet 2, sheet 3, sheet 4 etc., but those sheets should always pick up the dates defined in sheet 1 automatically.

Each sheet contains several measurement value cells, this is why there needs to be some additional empty row below the value cell to add comments (in sheet 2, sheet 3, 4, etc.).

Assumed we have this kind of table in sheet 1:

1  2021-01-01
2  2021-02-15
3  2021-03-23

In sheet 2 I want to copy the date column from sheet 1, but add an empty row between each subsequent date row, so the coworkers can add notes below values X, Y and Z:

1  2021-01-01    X-Value             U-Value
2                (comment to X)     (comment to U)
3  2021-02-15    Y-Value             V-Value
4                (comment to Y)     (comment to V)
5  2021-03-23    Z-Value             W-Value
6                (comment to Z)     (comment to W)

As soon as one adds a new date row in sheet 1, in sheet 2 etc. this row should appear with a blank row distance above.

Is there any possibility to do so?

[Lupp]

Would you be so kind (and clever) to explain the purpose, please. Good questions do,

[Pantona]

@Lupp: Thank you very much for your hint! I have added an explanation - I hope it will be understandable.

[Lupp]

Thanks for your readiness to explain.
Unfortunately the additional information doesn’t help me to understand your interjectional rows as a reasonable design.
As it seems you want to put a kind of headers there, but “headers” only addressing one row of data are just another bad idea in addition to the extra rows.
Generally sptreadsheets should be used for some kind of evaluation, and any evaluation I can think of will seriously suffer from problems caused by that design.
How many kinds of “X-and-whatever-Value” do you think can occur (all dates included)?
If there is no clear namespace for them, I would doubt if your sheets can ever be more than a digitalized scratch-paper. If there is one you may need a step of database-thinking, and create an extra sheet identifying them and allowing to refer to them [by IDs which can serve in the other sheets as foreign keys, or] at least as a validation base for a sequence of horizontal pairs of (Valuetype, Value).

[JohnSUN]

For your simple table, there is an easy way to do this. Insert the formula =CHAR(10) into column B and stretch it to the end of the data. Now copy all data with function results, go to a new sheet and paste the contents of the clipboard as a CSV

[Pantona]

Thanks for your help, but unfortunately this does not work as desired… I need to add empty rows automatically as soon as new text was inserted into sheet 1. Your function does only add a new empty line into the cells of sheet 1, but without adding “real” rows into the spreadsheet.

[JohnSUN]

Oh, excuse me, please - I misunderstood your wish " I want to copy those rows from sheet 1"… Do you want to do it automatically every time? And these blank lines - will they always remain blank? Or will something be recorded in them? Because a macro that is triggered every time the content on sheet 1 is changed can completely re-create the content on sheet 2 (if something appears in empty lines, it will be erased) In other words, @Lupp is right - the question must be more detailed, otherwise all the posted answers will be wrong.

[Pantona]

@JohnSUN: Thank you very much! Thanks to your request, I have updated my explanation. I hope you can understand better now.

[JohnSUN]

@Pantona Yes, now I understand better, I answered the wrong question. I know you will believe me not today or tomorrow - months will pass before you admit that I was right. But nevertheless, I will write this now - “You have chosen the wrong tool for solving your problem, you do not need Calc, but the Base”. Just try to answer the question - what happens if two or more measurements are taken in one day?

[schiavinatto]

Hi, @Pantona, test file

Macro linked to Spreadsheet Content Changed Events

REM  *****  BASIC  *****

Sub AlteredContentArea ( oCelula )
'------------------------------------------------------------------------------
'Por Grafeno: 'https://ask.libreoffice.org/t/como-executar-uma-macro-no-calc-ao-selecionar-uma-celula-de-uma-area/38209
' Testar se o objeto selecionado é uma célula individual '
If oCelula.ImplementationName <> "ScCellObj" Then Exit Sub
' Endereço da Célula '
oEnd = oCelula.CellAddress
' Celula no intervalo B7:B27 '
' Coluna 1 e linha entre 6 e 26 '
If oEnd.Column = 0 Then
If oEnd.Row >= 1 And oEnd.Row <= 50 Then
' Chamar a macro desejada '
Call Pantona
' -----------------------
End If
End If
End Sub


sub Pantona
'-------------------------------------------------------------------------
dim document   as object
dim dispatcher as object
'-------------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$Plan2.$A$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
'-------------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "StringName"
args3(0).Value = " "
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
'-------------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$Plan1.$A$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, Array())
'-------------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SetInputMode", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Cancel", "", 0, Array())
End Sub 

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[catlinger]

This seems doable under certain conditions: if there will be no attempt to change the order or number of existing dates on the first sheet, and if each subsequent sheet has its own permanently fixed number of blank lines per date.

One solution is to add a hidden column on sheets 2-N that acts as an index into the date column of sheet 1. The rows of this column contain this formula:

=INT((ROW()-1)/Blank_Lines)+1

where Blank_Lines is a constant defining the number of empty lines per date. In this case it may be best to simply use the number, so nobody is tempted to change it after data entry has begun.
In the date column of sheets 2-N you use something like this formula, assuming $A is the index column:

=IF(MOD(ROW()-1, Blank_Lines)=0,INDEX($Sheet1.$A$1:$A$32000,$A1,1),"")

Unfortunately this renders blank dates as existing, but with value zero. One workaround is to detect the condition and use an empty string out for a blank input. But it may be simpler to change the columns to a custom date format. In this case use “YYYY-MM-DD;;”. The semicolons suppress a zero date value.

The hidden column isn’t really necessary. You can proceed without if you don’t mind a longer formula in the date columns in sheets 2-N:

=IF(MOD(ROW()-1, Blank_Lines)=0,INDEX($Sheet1.$A$1:$A$32002,INT((ROW()-1)/Blank_Lines)+1,1),"")

To add column titles you’d change ‘ROW()-1’ to ‘ROW()-2’.

You can use the same technique to populate sub-row titles. Using the OP’s example with two lines per date, you might put this formula in the second column:

=IF(MOD(ROW()-1, Blank_Lines)=0,“Data:”,“Comment:”)

Further refinement would display the titles only on lines corresponding to an existing date.

I have attached a sample spreadsheet.
ColumnPropagation.ods (11.1 KB)