Sum all the value of matching name into second file

[harishnehru]

Hello All

I hope some expert can help for my problem.

I have two excel file. File1 contains short name, and value

File2 contains full name and total.

I want to add up all the values in the File1 for each short name that corresponds to the full name in the File2 and show the total in File2.

The ShortName are unique for each FullName.

Example:

File1

SiteName - ShortName- Value
AAA ------ Nick ------ 10000
BBB ------ Jack ------ 5000
CCC ------ Joe ------ 4000
DDD ------ Nick ------ 20000
EEE ------ Jack ------ 3000

File2

FullName ------ Total
Nick Smith ----- 30000
Jack Field ------ 8000


As the File1 will be updated when a new contracts have been won with SiteName, ShortName and Value, and the File 2 should be automatically update the Total for each Fullname.

If it is problem or longwinded to match the shortname with fullname, I can replace shortname with fullname in the File1.

I hope its clear and please reply if you need further details.

Many thanks for your time and help.

[arthurbr]

Why not replace Shortname with Fullname and use a Pivot Table
(in excel you have the option " Pick from List" when you right-click a cell enabling you to use already entered fullnames in the same column)l

Define your range as a dynamic range for the pivot table. More info on that :http://www.cpearson.com/excel/named.htm

HTH