Results 1 to 6 of 6

Thread: Automatic reflection of data from another workbook

  1. #1
    Neophyte SHERNEL's Avatar
    Join Date
    Jul 2018
    Posts
    3
    Articles
    0
    Excel Version
    2013

    Automatic reflection of data from another workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,011
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte SHERNEL's Avatar
    Join Date
    Jul 2018
    Posts
    3
    Articles
    0
    Excel Version
    2013
    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

  4. #4
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,011
    Articles
    0
    Excel Version
    Office 365 Subscription
    I think you need to provide a sample workbook.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Neophyte SHERNEL's Avatar
    Join Date
    Jul 2018
    Posts
    3
    Articles
    0
    Excel Version
    2013
    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
    Attached Files Attached Files

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •