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_Sinhathanks 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.
Thanks,
Garima.
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.
=INDEX(ActivitySheet!$A2:$A2,1)
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 03:10 AM
@Rajesh_SinhaThanks for the pointers as in VBA code, I am using that to achieve my requirement.
Jan 04 2021 04:42 AM
I might suggest that you use the following formula in column A of all created sheets:
=INDEX(INDIRECT("ActivitySheet!$A"&ROW()):INDIRECT("ActivitySheet!$A"&ROW()),1)
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
THEN
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".
Jan 04 2021 09:59 PM
Happy to learn that the VBA code is working for you,,, now if you feel you may accept my post as Best Solution as well Like.
Keep asking in case any other issue.