HELP! Worksheet Problem

[Malcolm]

Hi

I have a workbook containing 6 worksheets being Nov 01,
Feb 02, May 02, Aug 02, Nov 02 and Jan 03.

Each worksheet contains details of loans on these dates to
clients. Each client has a unique ID number and is in its
own row with columns A-I being ID, Prefix, First Name
through to post code etc and column J being the loan
amount.

The first worksheet (Nov 01) contains 76 clients, the
second (Feb 02) contains 189, (May 02) = 297, Aug 02 =
345, Nov 02 = 342 and the last (Jan 03) contains 350
clients.

I need a way to import all the details of the clients from
each worksheet to a consolidated worksheet. So if all the
clients in November 01 are also in Feb 02 & May 02, I
would like the consolidated sheet to show Empl Id in
Column A, Prefix Col B, First Name Col C, etc through to
postcode in Col I as in each worksheet. I would then like
to be able to put each loan in the following rows starting
at column J going in worksheet order. For example Col J
would be November 01, Column K could be loan amount for
Feb 02, Col L would be May 02 etc.

Any help would be greatly appreciated.

Many thanks

Malcolm

 

[Arvi Laanemets]

Hi

At start, it'll be a good idea to have a sheet p.e. Clients with columns ID,
Prefix, FirstName, etc, and define column ID and whole table (both without
header row) as dynamic named ranges (p.e. ID and Clients). In all your
monthly sheets you can then format ID column as Data.Validation.List with
source=ID, and in columns B through I you get client data through VLOOKUP,
like:
=IF($A2="","",VLOOKUP($A2,Clients,2,FALSE))

On Consolidation sheet, you enter ino cell A1 the formula:
=(IF(Clients!$A2="","",Clients!A2), and then copy it to range p.e. A2:I500
(you can have more rows to have the formula ready when you entre a new
client)

Into cell J2 enter the formula:
=IF(A2="","",SUMPRODUCT((Nov01!A2:A77)*(Nov01!J2:J77)))
Into Cell K2:
=IF(A2="","",SUMPRODUCT((Feb02!A2:A77)*(Feb02!J2:J77)))
etc

Copy cells J2:?? down for same number of rows, as you have links in columns
A:I


Arvi Laanemets