Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    need help to copy data from one file and paste it into another

    hello everyone;

    i am attaching two SMALL workbooks to exemplify the scenario, in reality Basic Data.xlsx [henceforth BD] is large enough so i created Sales Tracking.xlsx [hence forth ST], to see results.

    i update BD every Saturday with what Peter, Paul and Mary sold during the week.

    what i would like to have is a formula [link] that copies what Peter, Paul & May sold and pastes automatically in its corresponding sheet in ST.

    i unsuccessfully tried combinations of Vlookup, Index, Match, IF and even Pivot Tables; but somehow there is something I miss, and that's why i am "throwing the towel" and asking for help.

    any suggestion / advice / recommendation will be appreciated.

    TIA


    daniel rozenberg
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Daniel,

    Here's a partial solution but first some comments:
    1. I'd suggest doing away with the merged cells for the dates in the BD file as this makes it hard to do things like sorting and adding new people.
    2. The merged cell also causes unnecessary complication on formulas.


    Now here's the partial solution, it's partial because for some reason on my Laptop (Win 10 TP & Excel 2010) links will not update unless both files are open, this should not be!

    danielr.JPG
    When opening only the Sales Tracking file you get the message:

    Formula used: =SUMIFS('G:\BEKDocs\Excel\Test\[RG1-Basic Data.xlsx]Sheet1'!$C$2:$C$106,'G:\BEKDocs\Excel\Test\[RG1-Basic Data.xlsx]Sheet1'!$A$2:$A$106,$A2,'G:\BEKDocs\Excel\Test\[RG1-Basic Data.xlsx]Sheet1'!$B$2:$B$106,"Mary")
    danielr2.JPG
    Clicking Update should update the totals but instead it returns #Values for some formulas. Opening the BD file will immediately update the values! I'll be investigating this (done some already w/o success) further and maybe some one else who links to other files regularly knows the answer. Note: both files are in a trusted location on my machine.

    Test files: RG1-Basic Data.xlsx
    RG1-Sales Tracking.xlsx

    Note: I've only put formulas in the Mary sheet of ST.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    danielr2 (2015-05-09)

  4. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    Hi RG;

    as usual, i find your collaboration extremely valuable!
    after posting the thread, and a well deserved nap, i attacked the problem again.
    you're absolutely right, un-merging the date cells helped a lot, and meanwhile i found a solution.
    i am attaching the new files and will certainly appreciate your comments and suggestions about them.

    BR,

    dr
    Attached Files Attached Files

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Daniel,

    If you create a Named Range "Titles" on the BD sheet for $A$1:$E$1
    Then change your formula in ST to:
    =INDEX('[Basic Data SOLVED.xlsx]Sheet1'!$A$2:$F$106,(MATCH($A2,'[Basic Data SOLVED.xlsx]Sheet1'!$A$2:$A$106,0)+2),MATCH(B$1,'Basic Data SOLVED.xlsx'!Titles,0))

    You can now drag this formula down and right (adding new columns) and it will retain it's accuracy. Just remember to insert matching columns before the Totals column and then in ST just drag the formula over then down.

    danielr.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    thank you very much, RG.
    i will change the tag of thread to solved.

    BR,

    dr.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Daniel,

    I got to thinking (always a dangerous thing! ) and I came up with I think is the FINAL SOLUTION

    =SUMIFS(OFFSET('[Basic Data SOLVED.xlsx]Sheet1'!$A$2:$A$106,0,MATCH(B$1,'Basic Data SOLVED.xlsx'!Titles,0)-1,105,1),'[Basic Data SOLVED.xlsx]Sheet1'!$A$2:$A$106,$A2,'[Basic Data SOLVED.xlsx]Sheet1'!$B$2:$B$106,RIGHT(CELL("Filename",$A$1),LE N(CELL("Filename",$A$1))-FIND("]",CELL("Filename",$A$1))))

    This formula can be filled down and across and copied to other named sheets w/o modification of any kind. This is due to the pulling of the salesperson name from the tab containing the formula. Hope you find this useful it sure burned out a few grey matter cells getting it right.

    Now for the bad news, yes there always is, this formula still has the behavior, on my machine at least, of not updating the values in ST unless BD is open? I sure hope someone else has the answer to this vexing problem.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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