Search and sum

[mtlupo]

OK, I have tried to simplify this a little bit, hopefully there is something to work for this.

I have worksheet 1 column A that has a list of names. What I would like is for something to search through all the other worksheets in column A and anywhere that it finds the same name, it will sum up column E through column J for the row that it found that name in and display the total on worksheet 1 again.

Is that possible? Thanks.

[NBVC]

If you can create a new column in each of the sheets that totals each row from E to J, then here is a formula method....

First do as above and in say column K of each sheet sum values from columns E to J.

Then in the summary sheet, list the names of the sheets somewhere and name this list "TabNames". You do this by going to Insert|Name|Define.. enter "TabNames" (without quotes) in the Names in this workbook field. Then enter the range in the Refers to field.

Then use formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&TabNames&"'!A2:A100"),A2,INDIRECT("'"&TabNames&"'!K2:K100")))

where A2:A100 is the range in each sheet to check for matches and K2:K100 is the sum range.

A2 is the cell in the Summary sheet to look for within other sheets.