I'm trying to streamline a spreadsheet I do for a fantasy basebal
league, and if there's a way to do the following, that would be great:

We run our games on weekends and base our point system on how th
actual players perform.

I have a weekly stat section, and a season to date stat section (STD
awkward acronym, I know).

I would like to be able to make the STD section automatically update a
I enter the weekly stats (adding each new week's stats to the tota
from the previous weeks), rather than having to actually do the variou
additions manually.

I'm pretty sure the above is possible (I just don't know how to do it)
but I also need to have the values in the STD section remain the sam
when I reset each week to 0 without having it revert to 0 as well (i.e
after each weekend the STD value locks as the initial value to add th
new week's stats to).

Any help would be appreciated, and if I wasn't clear on anything, jus
ask and I'll clarify


-
bigjimfb
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message550603.htm

I think you'll need a macro for this kind of thing.

And that macro will depend on how your data is laid out.

I assumed that the data is is laid out exactly the same between the weekly and
the year to date worksheets. (I'd put hitters on one pair of sheets and
pitchers on another pair to make the layou simpler.)

I guessed that the names (no duplicates!) appear in column A and the headers are
in Row 1. Each field of each record will be added to the corresponding name in
the STD sheet. (Column 1 won't be copied over--it's the key between the sheet.)

The names don't have to be in the same order. (I'll bet you'll lose and acquire
players through the year).

If all that seems ok, then I wouldn't have it run automatic. (Automatic to me
means that it runs without your control--maybe as soon as you hit enter after
making a change on the weekly sheet. With my typing skills, this kind of thing
scares me.

But I'd add a button from the Forms toolbar to a the weekly worksheet to do the
combining. When you're done editting, you click the button. (and add another
button that clears the input range.)

Anyway, with all those caveats, here's something that might get you started:

Option Explicit
Sub transferStats()

Dim WeeklyWks As Worksheet
Dim STDWks As Worksheet
Dim res As Variant
Dim myCell As Range
Dim DestCell As Range
Dim iCol As Long

Set WeeklyWks = Worksheets("Weekly")
Set STDWks = Worksheets("std")

With WeeklyWks
For Each myCell In _
.Range("a2", .Cells(.Rows.Count, "A").End(xlUp)).Cells
res = Application.Match(myCell.Value, STDWks.Range("a:a"), 0)
If IsError(res) Then
With STDWks
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myCell.EntireRow.Copy _
Destination:=DestCell
Else
For iCol = 2 To .Cells(1, .Columns.Count).End(xlToLeft).Column
STDWks.Cells(res, iCol).Value _
= STDWks.Cells(res, iCol).Value _
+ myCell.Offset(0, iCol - 1).Value
Next iCol
End If
Next myCell
End With

MsgBox "done!"

End Sub
Sub clearWeeklyStats()

Dim WeeklyWks As Range
Dim resp As Long
Dim LastRow As Long
Dim LastCol As Long

resp = MsgBox(prompt:="Are you sure you want to clear" & _
" values in the Weekly Worksheet?", _
Buttons:=vbYesNo)

If resp = vbNo Then
Exit Sub
End If

Set WeeklyWks = Worksheets("weekly")

With WeeklyWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range("b2", .Cells(LastRow, LastCol)).ClearContents
End With

End Sub

The first routine looks for a match in column A. If it finds one, it merges the
data. If it doesn't find a match, it copies the row to the bottom of the STD
sheet.

The second routine clears that input range.


In the first routine, this is the line that says which columns to copy|merge:
For iCol = 2 To .Cells(1, .Columns.Count).End(xlToLeft).Column

You could change it to:
For iCol = 2 To 18
if if you know what columns to copy

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




bigjimfbb wrote:

I'm trying to streamline a spreadsheet I do for a fantasy baseball
league, and if there's a way to do the following, that would be great:

We run our games on weekends and base our point system on how the
actual players perform.

I have a weekly stat section, and a season to date stat section (STD,
awkward acronym, I know).

I would like to be able to make the STD section automatically update as
I enter the weekly stats (adding each new week's stats to the total
from the previous weeks), rather than having to actually do the various
additions manually.

I'm pretty sure the above is possible (I just don't know how to do it),
but I also need to have the values in the STD section remain the same
when I reset each week to 0 without having it revert to 0 as well (i.e.
after each weekend the STD value locks as the initial value to add the
new week's stats to).

Any help would be appreciated, and if I wasn't clear on anything, just
ask and I'll clarify.

--
bigjimfbb
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message550603.html


--

Dave Peterson