Suming a column to another worksheet

[CRYM]

Hello everyone,

I am new to Excel programming, however, I am a software developer.
thought a forum would be best place to get my question answered. Here'
what I want to do:

On the first worksheet I have a column (multiple rows) that can hav
one of 23 names entered in it. In the column next to that (als
multiple rows), a dollar amount is entered.

On the next worksheet I have all the names on different rows, wit
their total amount. I'm assuming I would need to code some sort o
macro or something to take the amounts for each person on the firs
worksheet and add it to their individual totals on the secon
worksheet, correct?

If someone could point me to either a tutorial that would demonstrat
how to do this, or maybe a snippet of code, that would be great.

Thanks!

Chri

 

[duane]

it sounds like the sumproduct function can do what you want

check this out and see if it will do what you need - it does wor
across (even closed) worksheets.

http://www.xldynamic.com/source/xld.SUMPRODUCT.htm

 

[Guest]

Hi there and welcome to the wild, wooly world of Excel programming. Here's
some code that should accomplish what you want. I've made a couple of
assumptions for the code below to work so you'll need to do this also.
(1) Create a range name called "CurrAmountsList" for the two columns (names
and amounts) in your "current" dollar amounts list (note that the range can
have more rows than the data currently within the range; it's just handy to
have an identifiable range name that holds the maximum amount of names and
amounts that you expect to be there)
(2) Creat a range name called "AccAmountsList" for the two columns in your
accumulated dollar amounts list (this is presumably the names of all the
folks for whom you are accumulating bucks).
P.S. If you don't know how to set up a range name, drop a reply post here
and I'll tell you how.

Sub AddBucks()
'Adds dollar amounts for individuals on Sheet 1 to the accumulated
'totals for those individuals on Sheet2

Dim i As Integer
Dim CurrName As String, CurrBucks As Single
Dim FindName As Integer
Dim NameCount As Integer

On Error Resume Next

NameCount = Application.WorksheetFunction.CountA(Range("CurrAmountsList"))

For i = 1 To NameCount
'Select the first individual in the current amount list and set
their name
'and current dollar value
CurrName = Range("CurrAmountsList").Cells(i, 1).Value
CurrBucks = Range("CurrAmountsList").Cells(i, 2).Value
'Determine where this individual is in the accumulated totals list
FindName = Application.WorksheetFunction.Match(CurrName,
Range("AccAmountsList").Columns(1), 0)
Range("AccAmountsList").Cells(FindName, 2).Value = CurrBucks +
Range("AccAmountsList").Cells(FindName, 2).Value
Next i

End Sub