Dec 20 2020 07:28 PM
Dec 20 2020 07:28 PM
I have a scenario, worksheet 1 column A gets its data from worksheet2 column A, I have linked the two so that's easy, but if I delete or add a row in between worksheet 2 that doesnot get reflected in worksheet1, how do I achieve that, TIA.
Dec 20 2020 08:06 PM
Getting data from one to another sheet can be better handled by Lookup formula either VLOOKUP or INDEX & MATCH,,, in case of delete you may wrap these with IFERROR.
Another is Paste Link,,!
Reply in case of further assistance, and better share the WB with us along with formula/method U r trying so far.
Dec 21 2020 10:42 PM
@Rajesh-Sthanks for replying, actually my requirement is very simple, vlookup is good for match n verify but I need to fill 3 worksheets of similar nature from one point, I have attached excel snapshots, where 1. file has 3 months activities for each month (dec-2020,Jan-2021...), the first column has all the activities listed for that month.
2. a worksheet I want to create such that first column of every month can be filled, updated through this one sheet otherwise any addition or deletion of activities needed to be updated for all 3 months one by one.
3. adding a row in ActivtyList tab shd reflect in all three
4. similarly for deleting a row
to fill the first column for all months, I am linking the column A to ActivityList columnA using paste link
hope there is simple scripts available to do this task since its not going to be huge data just a regular updates.
Dec 22 2020 02:27 AM
The most easiest is INDEX formula:
Enter this in cell A2 of destination sheets, and copy down till needed.
Another is VBA macro:
Private Sub Worksheet_Change(ByVal Target As Range) Dim i, Lastrow As Long If Target.Column = 1 Then Lastrow = Sheets("ActivitySheet").Range("A" & Rows.Count).End(xlUp).Row Sheets("Destination1").Range("A2:A100").ClearContents For i = 100 To Lastrow Sheets("ActivitySheet").Cells(i, "A").EntireRow.Copy _ Destination:=Sheets("Destination1").Range("A" & Rows.Count).End(xlUp) End If Next i End If Range("A1").Select End Sub
Jan 04 2021 04:42 AM
I might suggest that you use the following formula in column A of all created sheets:
I am thinking that if you have a sheet called "ActivitySheet", and in the first column A of that sheet called "ActivitySheet" you list all the activities on each row in that column A
in the other created sheets, you put the above formula in each row of column A,
then column A of all the created sheets will have the same as column A of the Sheet Called "ActivitySheet"
So that means that you can add or delete any row in the "ActivitySheet" and the created sheets will all have the same data in column A as is in column A of the "ActivitySheet".