Data range copy and paste if any change in the row

[harinsh]

Hi Team,

I need some help, I have 7 columns data in sheet1 and this is the reference of original data from Sheet2 (through the formula) both data sets are same.

I am not sure if this can be done via worksheet change request. If I change the cell value in any rows then that entire row till data set should get the copy and paste to corresponding data in sheet2.

Is this something can be achieved? Thank you

 

[mumps]

To clarify:
The data in Sheet1 is identical to the data in Sheet2 through the use of formulas in Sheet1. When you change a value in Sheet1, you want that row of data pasted to the same row in Sheet2. Is this correct? When you change a value in Sheet1, you will be overwriting the formula. Is this correct?

 

[harinsh]

yeah, when I change the value in sheet1 it should copy and paste the entire changed row (till data ends) in sheet2, the tricky part here is the data is identical but not necessary in the same line . for example ..if I have ABC as name in Sheet2 row #10 but in Sheet1 it might be at row number 20....becuase I built formula based on my multiple criteria in sheet1. I only have the option to get uniqueness is via concantinate multiple values...I am fine even in overwriting the formulas in sheet1 because if I do CTRL+D I get back the formula.

 

[mumps]

We need to have a way of identifying each row. This is usually done with a unique value in each row that we can use as a reference. Are you saying that the only way of getting that unique value for each row is by concatenating multiple values from each row? If so, which values? I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[harinsh]

Hi mumps,

Many thanks for addressing this question. I have uploaded the sample working file....you can see the sheet1 and sheet2 data.
https://www.dropbox.com/s/um6qwl51zflp0yh/Sample.xlsx?dl=0

Let me know in case if cann't download the file.

Thank you again,

 

[mumps]

Are the changes in Sheet1 made manually (this would over-write the existing formulas) or are the changes in Sheet1 the result of the formulas when changes are made in Sheet2?

 

[harinsh]

Yes, Sheet1 changes done by manually, let them over-write the existing formula ...its fine...imp for me all changes should get capture in sheet2

 

[mumps]

Let's work with a couple of examples. Let's say we change the date in cell D7 of Sheet1. How can we determine in which row in Sheet2 the data will be pasted?
What if we enter a name in cell C4, which is currently blank, in Sheet1, how can we determine in which row in Sheet2 the data will be pasted?

 

[harinsh]

If change date in D7 then entire set of data i.e from B7:G7 should copy and paste in Sheet1, to identify only need help, if I concatenate Name/Date/Place and same concatenate should be build in Sheet2 ...can we do index/vlooup to get those in Sheet2....Its just my thought but don't how efficient is this..

In case any blank..it should go to last row of data and add as new line....

 

[mumps]

If we change the date in D7, the problem with using Name/Date/Place is that in Sheet2 there are 2 rows with the same Name/Date/Place, rows 13 and 26.