Dynamic Copy Data between Sheets

[elgre]

Hi everyone,

I have a questions regarding copying the data between the sheets.

I have a sheet, lets call sheet1, and I want to create a new sheet sheet2, which gets partial data from sheet1 and also updates itself based on the updates on sheet1.

For instance, lets say I have the data in B2:O1100 in sheet 1. Firstly, the sheet2 should get the data from C2:I1100 and M2:O1100 by also keeping the formatting. Then, at any point, if I make any changes in sheet1, this should be captured in sheet2 as well, i.e. if I add a new row to row457 in sheet1, this row should be added to row457 in sheet 2 as well and the data in the new row of sheet1 from cells C.D,E,F,G,H,I,M,N,O should be copied to the new row in sheet2 or if I change the data in the cells of sheet1, this should change in sheet2, too.

I tried a couple of options from online resources and tried to manage it by using macros but I have very limited knowledge on this. Any help would be much appreciated!

Thanks a lot in advance.

 

[Jeffrey Mahoney]

There are two schools of thought, as far as I'm concerned.
1 You can manage Sheet2 line by line
2 You can manage Sheet2 by copying the entirety of Sheet1 after each change

Option 1 is harder, especially if you're trying to make Sheet2 look identical to Sheet1. Usually, I would set up a database and have a unique key field with an item number or serial number to manage your records. I don't know why you would need to have each record on the exact same row on both sheets. You only want to update certain columns from sheet1 after the initial copy over to sheet2; ok, I'll bite... Please explain so I can help further.

Jeff

 

[elgre]

Thanks a lot for your reply Jeff!

I think I should upload an excel example file to be able to explain it a bit more clearly Example.xlsx.

What I am trying to do in this example excel is to copy the data in sheet1 to sheet2 automatically. But also at the same time, I want to capture the sheet1 updates in sheet2 automatically as well, especially adding a new row. I managed to do it until some point by using Index function. For instance, whenever I add a new row in the sheet1 regardless of where added, the sheet 2 captures it. But the problem is that I should have enough "empty rows" in sheet2 to be able to capture new rows added into the sheet1.

In the example file, you will see that I have 2 empty rows at the end of the sheet2. So this index functioned excel will allow me to capture only 2 new rows of sheet1 in sheet2 regardless where I add them. However, if I add the third one, it will start eating up the data, i.e. if I add third row in sheet1, the row with Hon. Lilian in sheet2 will disappear, which is a problem.

I hope what I am trying to achieve is much more clear now but please let me know if you need more explanation.

 

[Jeffrey Mahoney]

That link requires agreeing to some terms of service. I'm hesitant to click.

Whenever I help someone I usually look at the bigger picture. In this case, I'm trying to figure out why you need sheet2 to be identical to sheet1 using formulas.

 

[elgre]

The reason that I try to make it identical is to make it more readable. The file I share is only an example. In reality, I have much longer data, which starts from column A end in column Y. I am trying to extend this list beyond column Y and if I do this in the same sheet, it will be really difficult to scroll right-left to read the data. Therefore, I wanted to create a new sheet with only the columns that I need, then I can add the new columns to the end, which will be much easier to read and follow.

Re the link: No problem, I appreciate your concern. It is WeTransfer link actually, I could not find any other way to share the file but I am open to any suggestions to be able to share.