Automatic reflection of data from another workbook

SHERNEL

SHERNEL

New member
Joined
Jul 12, 2018
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2013
I have a tracking sheet where a persons sends me the details via mail and I have to update the data in the tracking sheet in their particular colums
Each column has a unique ID which is filtered out and the details for the ID is entered

Is there a turn around where I do not have to manually filter out the ID by searching them and updating the data
one by one rather I paste the data at one place and it should reflect in the tacking sheet in their respective ID

Please help

Thanks in advance
 
Convert your data entry range to a table - you can then add new data at the bottom and then sort on whichever field (column) you wish.
 
Thanks for your answer.

Just a little more info regarding my question

In my excel document there are thousands of IDs for example 120-1 , 120-2 etc each of them has their own start and end date.

Now a guy would mail me the start and end date of 120-1 but I do not want to go to the sheet and manually update it as there would be many people sending me the status for different IDs

Instead is there a way to link a sheet to another where I just copy the data from the mail which the person has sent me and paste it into another sheet on doing it which should reflect in the main sheet for just that particular ID
 
The SMR.xlsx is the main worksheet where only the ID would be provided.

The person will send me the details (Please check the "Detail" file).

Now I have to copy the detail provided to the main worksheet and the detail sheet would be getting updated frequently
 

Attachments

  • SMR.xlsx
    8.5 KB · Views: 9
  • Detail.xlsx
    9.3 KB · Views: 6
Open both your attachments. Add the following code to a standard code-module in SMR.xlsx. Run that code. If it works, save the SMR file as .xlsm.
Code: 
Sub blah()
Dim FoundCell As Range
Set TgtSht = Workbooks("SMR.xlsx").ActiveSheet
Set SceSht = Workbooks("Detail.xlsx").ActiveSheet
Set TgtColm1 = TgtSht.UsedRange.Columns(1)
For Each cll In SceSht.UsedRange.Columns(1).Offset(1).Cells
  Set FoundCell = Nothing
  Set FoundCell = TgtColm1.Find(cll.Value, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False, searchformat:=False)
  If FoundCell Is Nothing Then
    Set FoundCell = TgtColm1.Cells(1).Offset(TgtColm1.Rows.Count) 'if not found in target sheet, add a new row to that sheet.
    Set TgtColm1 = TgtColm1.Resize(TgtColm1.Rows.Count + 1)
    FoundCell.Resize(, 7).Value = cll.Resize(, 7).Value
  Else
    FoundCell.Offset(, 1).Resize(, 6).Value = cll.Offset(, 1).Resize(, 6).Value
  End If
Next cll
End Sub
 
You must log in or register to reply here.
Back
Top