MrExcel Publishing
Your One Stop for Excel Tips & Solutions

linking to data in another workbook

Posted by TPR on March 21, 2001 11:25 AM

I have never posted to this forum and am an extreme novice. I have little experience with formulas and none with macros. So any help would be greatly appreciated.

We have a report downloaded into a spreadsheet from our ERP system with part numbers and comments (just text in cells, not inserted comments) which is updated each week. Each week a part number might show up on the new report which was also on the previous report. Is there a way to automatically pull the comments from the previous report to the new report based on the part number which is in a different column?

Ex. comments are in column A and part numbers are in column K. Part number 123 is on row 38 on the previous report but shows up on row 52 on the new report. I want any comments in A38 pertaining to part number 123 on the previous report to be transferred to A52 on the new report. This must also be done for all parts which are repeated on the new report. The report usually has about 1500 line items with maybe 75% being repeats from the previous report but always in a different arrangement. It seems like this should be pretty simple but I can't figure it out.


Posted by tom venn on March 21, 2001 4:11 PM

try this formula by putting the formula in cell L2: =INDEX('old report'!$A$1:$K$65534,MATCH('new report'!K2,'old report'!$K$1:$K$65535,0),1)

-- assume you have two worksheets (tabs), "old report" and "new report". assume all part numbers are unique.