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

Topic

New Reply

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

Reply | Quote

Replies

Daniel,

Here’s a partial solution but first some comments:

[*]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.
[*]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!

40537-danielr
When opening only the Sales Tracking file you get the message:

Formula used: [noparse]=SUMIFS(‘G:BEKDocsExcelTest[RG1-Basic Data.xlsx]Sheet1′!$C$2:$C$106,’G:BEKDocsExcelTest[RG1-Basic Data.xlsx]Sheet1′!$A$2:$A$106,$A2,’G:BEKDocsExcelTest[RG1-Basic Data.xlsx]Sheet1’!$B$2:$B$106,”Mary”)[/noparse]
40538-danielr2
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: 40539-RG1-Basic-Data
40540-RG1-Sales-Tracking

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

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Daniel,

If you create a Named Range “Titles” on the BD sheet for [noparse]$A$1:$E$1[/noparse]
Then change your formula in ST to:
[noparse]=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))[/noparse]

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.

40548-danielr

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

thank you very much, RG.
i will change the tag of thread to solved.

BR,

dr.

Reply | Quote

Daniel,

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

[noparse]=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),LEN(CELL(“Filename”,$A$1))-FIND(“]”,CELL(“Filename”,$A$1))))[/noparse]

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.
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote