Sheet data

[Malcolm]

Hi

I have a spreadsheet which contains 6 worksheets being Nov
01, Feb 02, May 02, Aug 02, Nov 02 & Jan 03.

Each spreadsheet contains details of client loan
information with Nov 01 holding 76 records with each later
dated sheet holding more records than the previous.

The empl ID is the unique field for each client and some
clients are on all sheets whilst others may only be on the
last sheet.

What I need to do is to have all the information of loans
assigned to each client created in a new sheet.

For example, if a client from Nov 01 has loans on all six
sheets, I would like the new work sheet to show the empl
ID, prefix and address details in the first columns with
the loan details being shown in the columns following.
Colum J may show the loan in November 01, column K may
show the loan from Feb 01 etc.

Any help with this will be greatly appreciated.

If you need more info, please contact me by email at
(e-mail address removed).

Regards

Malcolm Davidson

 

[Bob Phillips]

Malcolm,

Suggestion.

Copy all of the ids to the Summary sheet, A2 down.
In B1 enter 'Nov 01 note the leading apostrophe to make it text
In C1, 'Feb 02, etc
In B2, add this formula
=IF(ISNA(MATCH(A2,INDIRECT("'"&$B$1&"'!$A$1:$A$100"),0)),"",INDIRECT("'"&B$1
&"'!B"&MATCH(A2,INDIRECT("'"&B$1&"'!$A$1:$A$100"),0)))
this assumes the ids on Nov 01 etc are in A2:An, and that the values you
want are in column B