Hi!

Assume you want to total sales amounts for client SMITH from all of your
sheets. All the sheets have the identical format in that client names are in
column A and the sales figures to sum are in column B.

If you only have 2 or 3 sheets you may want to use this formula:

=SUMIF(Sheet2!A1:A100,A1,Sheet2!B1:B100)+SUMIF(She et3!A1:A100,A1,Sheet3!B1:B100)

Where A1 is the clients name.

If you have more than 2 or 3 sheets:

Make a list of the sheet names somewhere, say, D1:D10. Then use this
formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D10&"'!A1:A100") ,A1,INDIRECT("'"&D1:D10&"'!B1:B100")))

Biff

"Sue" wrote in message
...
Can excel pick up totals on other sheets and workbooks for clients, where
the
clients information is never on the same row no. on each sheet, without it
being a lengthy case of having to alter the formula to accomodate the cell
change address? In other words how do I get it to recognise the name
rather
than the cell for picking up the info.?
The worksheets show money coming in during each 3rd of the year from
clients
and the total. On a summary sheet I want excel to pick up those 1/3rd
totals
for each client so I can then total those to give the yearly figure for
each
client. The reason the cell address is not consistent on each 1/3rd
period
sheet is because each client's time span with us is only ever a maximum of
2
years but usually less. So that means the current clients move up the row
on
the sheet as other clients finish hence the different row numbers.