Excel add points every day to track a total

chris pike
chris pike used Ask the Experts™
HI Experts,
I have an interesting problem. It is an Excel, points, and calendar challenge.
I will break this question down into a few questions if I can.

Question 1:
Just imagine you work on a team and every day each player on the team gets a base rate of one point every day. (this is one point at the start but can increase to 1.5 or 2.0 at a later date). Now the goalie has a very hard job so he gets 1.5 additional points everyday on top of the base points.

So here is the logic.

(x)- Person
(y)- base rate
(a)- bonus
X+Y+A = points per day

This is how I am doing this points tracking now….Look at how many columns there are. I need a way to automate this.
Points every day added up and totallingpoints-daily_1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MontoyaProcess Improvement Mgr
hey Chris. are you looking for the items to be added automatically each day, or are you just trying to make sure there is a value for each day off the calendar?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
It would be better to refer to column letters or cell addresses instead of X, Y and A.

Do you expect the workbook be updated starting at column 'P 'to today and every day afterward, or just today and every day afterward?

Not that it has anything to do with your question, but why isn't there a formula in F7?
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
The attached workbook contains this macro which will run every day at 8:00 AM. To test it without waiting until 8 AM, you can place your cursor in the macro (which is in Visual Basic, Module1) and press F5.
Sub TotalAvailable()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngLastColumn As Long
Dim lngColumn As Long

lngLastRow = Range("B1048576").End(xlUp).Row
lngLastColumn = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

For lngColumn = 8 To lngLastColumn
    If IsEmpty(Cells(3, lngColumn)) Then
        For lngRow = 3 To lngLastRow
            Cells(lngRow, lngColumn) = Cells(lngRow, "C") + Cells(lngRow, "E")
        Next
    End If
Next

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Open in new window

29165634.xlsm
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi Martin,
Only from today moving forward. Once we have a "snapshot" of the totals then we just move forward.
Not sure why missing formula...It's just an example sheet. it should total just like the other rows.

My second question was going to be how do we input "reason" or "note" when there is a change.
Looking at your sheet now.
Ok I made a couple changes. t o the sheet.
I am posting a second question to make further changes.
I am closing this question.
Stay tuned.
Sorry Montonya just saw your message late.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial