Need to create new sheet by merging 2 sheets

Rakesh Lavishetty

Hi Community,

Hope all are doing well,

sheet 1 .jpg

sheet 2.jpg

I need to merge sheet 1 and sheet 2 by creating new sheet i,e sheet 3.

Ex: sheet 1 + sheet 2 = Sheet 3

here i need to make sheet 3 dynamic i,e if I add any data in sheet 1 & sheet 2 , it should automatically reflect in sheet 3.

I m attaching 2 screenshots please suggest how to achieve this.

Genevieve P.

Hi @Rakesh Lavishetty

Yes, you can merge the two sheets using a Report which would allow you to keep the two sheets separate, but display all their content together in one item.

First, create a Row Report:

Then, select both sheets to pull rows from:

Next, select all of the columns to Display:

Finally, you may want to Group by the Sheet Name so that the data is organized into two different parts:

You could Group by different criteria or choose to Sort the Report to have your rows display in a different order and this won't change the underlying sheet. However you can still edit cell content from a Report, which will sync with the underlying sheets. Is this what you were looking to do?

This Help Article has more information on Reports: Build a Row Report with Report Builder. You may also want to check out the following webinar: Redesigned Reports with Grouping and Summary Functions.

Cheers,

Genevieve

Genevieve P.

Hi @Rakesh Lavishetty

What about creating a Report that puts together these two sheets? A Report would allow you to make changes which will then update the sheet, and vice versa. (See: Create a Report)

The only issue you may have is that Reports don't show hierarchy. In order to identify which rows belong with September and which belong to October, I would add in a column to each sheet which has the formula:

=PARENT([Task Name]@row)

This will bring the label "September" or "October" into each of the Child Rows. You could then Group the Report by this helper column and it will re-create a sort of header row for your data to fall under. Would this work for you?

Let me know if you'd like to see screen captures of what I'm describing and I'm happy to clarify further.

Cheers!

Genevieve

Rakesh Lavishetty

Hi @Genevieve P ,

Hope you are doing well,

Thanks for taking your time and writing to me.

The report idea which you have suggested will not working for my scenario,

Have to create a new sheet, because i have huge data sheet 1 (13 columns) and sheet 2 (32 columns), could you please suggest me can we achieve this by cell linking??? if yes, please suggest how to achieve this. thanks!!!

Regards,

Rakesh.

Genevieve P.

Hi @Rakesh Lavishetty

Can you clarify why a Report wont work for your scenario? It would be helpful to know a bit more about your process. You can add all 45 columns into a Report.

The alternative would be copy/paste all your data into one big sheet (sheet 3) but then create two Reports to use instead of your current sheet 1 and sheet 2.

Cell-linking is not a good solution for this large number of columns/cells because the limit of the number of cells from the same source sheet is 500 cells (see: Cell Linking). You could potentially build a column formula to copy over data, but there is a limit for cross-sheet references as well (see: Cross-Sheet Formulas).

Cheers,

Genevieve

Rakesh Lavishetty

Hi @Genevieve P ,

The task is to create a report in the end, but the primary task is need to merge the both sheets i,e sheet 1 and sheet 2. can we achieve this task without merging 2 sheets, if possible will go ahead and will create a report. please suggest how to achieve this!!

Genevieve P.

Hi @Rakesh Lavishetty

Yes, you can merge the two sheets using a Report which would allow you to keep the two sheets separate, but display all their content together in one item.

First, create a Row Report:

Then, select both sheets to pull rows from:

Next, select all of the columns to Display:

Finally, you may want to Group by the Sheet Name so that the data is organized into two different parts:

You could Group by different criteria or choose to Sort the Report to have your rows display in a different order and this won't change the underlying sheet. However you can still edit cell content from a Report, which will sync with the underlying sheets. Is this what you were looking to do?

This Help Article has more information on Reports: Build a Row Report with Report Builder. You may also want to check out the following webinar: Redesigned Reports with Grouping and Summary Functions.

Cheers,

Genevieve

Rakesh Lavishetty

Hi @Genevieve P ,

Thankyou so much for your time,

The above screenshots and content exactly what i have looking for, this means a lot to me.

Regards,

Rakesh.

Genevieve P.

Hi @Rakesh Lavishetty

I'm so glad this will work for you! Let me know if you have any other questions about Reports.

Genevieve