columns / rows messed up

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

Hi all,

I have 2 worksheets with data which I would like to link for a total
overview, unfortunatly colunms and rows are messed up in the way of
organizing the data (see below).

Sheet1
Name Amount
Jack 12,00
Rob 11,00
Mike 45,00
Jean 78,00
Doug 66,00
Susan 10,00
Pat 9,00



Sheet2
Name Jack Rob Mike Jean Doug Susan Pat
Amount 45,00 87,00 67,00 45,20 45,20
55,00 70,00


Fortunatly the index (Name field) is consequent (as I can see).

Is there a method I can link the sheets in a way that I can calculate the
amount totals?

Thx for suggestions.
Ludovic
 
In B2 (under "Jack") enter
=SUMIF(Sheet1!$A$2:$A$20,Sheet2!B1,Sheet1!$B$2:$B$20)
Change the cell references to fit your case (I used 20 rows, I expect you
have more)
Change commas to semi-colons in formula if that is what is used in your
local Excel version
Copy across the row
best wishes
 
Back
Top