Merge data from multiple sheets into one master sheet (not only once)

[opa1]

Hi,

I want to merge data from two sheets (Sheet2 and Sheet3) into one master sheet (Master).
My current formula for copying the data from only Sheet2 into the Master sheet is:

=IF($Sheet2.A2<>"";$Sheet2.A2;"")

Now I got a new Sheet3 where I want to copy this data into the Master sheet too.
But how can I do that?
Also I want to be able to add entries into Sheet2 and Sheet3 after the merge was done, so that it is done again. Not sure if this is clear, but I want it to be done not only once but every time I enter new data into the Sheet2 and Sheet3.
It does not matter to me how the data is sorted in the Master sheet as I can sort by a different column afterwards.

Attached is an example document.

Thank you very much in advance for your help!

merge.ods (9.2 KB)

[karolus]

Hallo
Copy and paste still exists!

@opa1

Also I want to be able to add entries into Sheet2 and Sheet3 after the merge was done, so that it is done again. Not sure if this is clear, but I want it to be done not only once but every time I enter new data into the Sheet2 and Sheet3.
It does not matter to me how the data is sorted in the Master sheet as I can sort by a different column afterwards.

And again the Solution is… forget Sheet2 and Sheet3 after the c&p and enter the Data directly into »master«

[opa1]

Hello karolus,

I know but it is not that easy as it seems.
The example file is just to illustrate my issue.
Sheet2 and Sheet3 have multiple different columns which are manipulated in Sheet2 and Sheet3 to be then copied to the master sheet.
Also the entry of B2 in the master sheet can come from C2 in Sheet2 or E2 in Sheet3.

[dscheikey]

Hi @opa1!
You can combine your two data entries with the following formula:

=IF(
        ROW()-1<LOOKUP(2,1/($Sheet2.A$1:A$1000<>""),ROW($Sheet2.A$1:A$1000)),
            OFFSET($Sheet2.A$2,ROW()-2,COLUMN()-1),
            OFFSET($Sheet3.A$2,ROW()-1-LOOKUP(2,1/($Sheet2.A$1:A$1000<>""),ROW($Sheet2.A$1:A$1000)),COLUMN()-1)
)

Then simply copy it down. If you have more than 1000 entries in Sheet 2 or Sheet 3, you need to adjust the formula.
Please see the very related post of mine in @rossalanblue question.:

Consolidate 3 modified sheets onto Main page Alphabetically and by Date

Hi Ross, (1) you can add cells and delete rows as you like in the lawyer sheets. In the Helptable, I always start from cell A2 and add as many rows as there are. You should not leave any free rows between the data, otherwise they will also be displayed in the consolidation sheet. (2) To change the lawyer names now, you only need to change the sheet name in the tab (by double-clicking on the tab) in the enclosed version. The formulas will now adjust to this in all places. (schooling) To unders…

merge.ods (14,2 KB)