Automated Synching of Columns in Separate Workbooks

[jwatts]

Not sure this is even possible, but I've been tasked with the following challenge:

As a regional nonprofit, the organization for which I work has kept two separate spreadsheets. The first sheet lists all donations made to the organization since its inception by donor such that multiple donations made by the same individual would still have the same ID number.

eg.

1 Jane Doe Date Amount
2 John Doe Date Amount
2 John Doe Date Amount
3 Jim Doe Date Amount

The second spreadsheet contains donor contact information sorted by Donor ID Number.

I need to find a way to sync the two workbooks such the donations made sheet sorts by the Donor IDs on the second sheet rather than by the arbitrary numbering system currently being used. While I could go through and do all of this manually, the list is rather extensive, and I have been trying to find a way within Excel of automating the process without actually merging the two workbooks into one.

Our Excel guru has recently retired, and as the unpaid intern, this project was tossed into my lap. Any help at all would be greatly appreciated as I am not highly familiar with many of Excel's more advanced features.

Thanks!

Jo

[JBeaucaire]

Assuming

1) the Donor IDs in sheet2 are in column A
2) The donor names are on sheet1 in column A and sheet2 column B

Do this:

1) Add a column to sheet1 to bring over the Donor ID from sheet2. Let's use E2 for the first entry and it will match by the name in a2 of this row. So in Sheet1 E2 put this formula:

=INDEX(Sheet2!A:A, MATCH(A2, Sheet2!B:B, 0))

2) Once the first DonorID appears correctly using the formula above (or after you tweak to work on your sheets), copy the cell down the donations list to get all the donorIDs to appear in column E

3) Highlight all the data in Sheet1 and sort the data by column E.

[jwatts]

1) Add a column to sheet1 to bring over the Donor ID from sheet2. Let's use E2 for the first entry and it will match by the name in a2 of this row. So in Sheet1 E2 put this formula:

=INDEX(Sheet2!A:A, MATCH(A2, Sheet2!B:B, 0))

Rather then being on separate sheets in the same workbook, the two sets of information I'm working from are in two completely separate workbooks. I've also been told that the center would prefer to keep them separate. Thank you very much for the formula -- I had been playing with the MATCH function a little, but wasn't sure how to format it. Is there any way to link sheets in different workbooks, or do they have to be in the same one?

Thanks again, especially for the prompt response!

-Jo

[JBeaucaire]

You can INDEX/MATCH between workbooks, too. It's no different than any other function that references a separate workbook.

1) Open both docs so the formula is simple to create. Once you close the reference workbook, Excel will expand the formula in the second workbook to include the full path, and you don't want have to do that manually, so start with both books open.

2) Then the same formula with a workbook reference:

=INDEX('[Book2.xls]Sheet2'!A:A, MATCH(A2, '[Book2.xls]Sheet2'!B:B, 0))

[jwatts]

Thank you so much. You've just saved me an entire day of banging my head against a wall.

Best,
Jo

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.