Thread: Match and Update in Workbooks

Hi All,

I need some help pls.

I want to link to a workbook and match/update the data in another workbook.

The Scene:

Master.xls

Contains 3 worksheets, renamed:

Sheet 1: Dec 10 – Feb 11
Sheet 2: Mar 11 – May 11
Sheet 3: Jun 11 – Aug 11

A B C D E
1 Date
2 Name 14-Dec-10 15-Dec-10 16-Dec-10 17-Dec-10
3 John Yes No Yes Yes
4 Sue No Yes No Yes
5 Chris Maybe Yes Yes Maybe
6 Kate Maybe No No Maybe

New.xls

Contains 1 worksheet:

A B C D E
1 Date
2 Name 14-Dec-10 15-Dec-10 16-Dec-10 17-Dec-10
3 John
4 Sue
5 Chris
6 Kate

The Challenge:

At the start of the month, the administrators of Master.xls will roll the 3x worksheets forward, e.g.

Sheet 1: Dec 10 – Feb 11 --> Jan 11 – Mar 11
Sheet 2: Mar 11 – May 11 --> Apr 11 – Jun 11
Sheet 3: Jun 11 – Aug 11 --> Jul 11 – Sep 11

I want Excel to:

1. Search all 3x worksheets in Master.xls
2. Match the name/date cells, e.g. Sue/16-Dec-10
3. Update Sue's satus in New.xls to display whether Yes, No, Maybe

I obviously can’t just use cell = as the worksheets are renamed each month which breaks the link.

Hope I’ve made it clear, thanks for looking and even a bigger thanks if you can help.

Stef
Excel Novice