How to create a daily sales tracker file when figures are altered every day?

hi all,
please refer the attached excel file, which is basically a daily sales volume tracker. it has 3 sheets (as of now) "Main", "YTM", & "Score Card".

This file already has a macro in sheet "main" (created with the help of user "OllyXLS"). The macro allows the existing figure in Column J& K to add up or subtract once a figure is entered in Column G & H. Through the simple formula, the rest of column gets automatically updated once the ENTER key is pressed.

Now I wanted to create a "date-wise" sales figure tracker in the sheet "Score Card". My thought was;
1) Every day the Cell under 'Invoiced on' heading will be changed to yesterday's (22 for tomorrow) and figures filled up.
2) Once the data is entered, the column in "Score Card" has to get updated for the Total of values in column G&H from sheet "Main" against each staff name under that date.
i.e., once i finish entering data in Column G & H on day 21 in sheet "Main" for Ahmed Kamal; sheet "score card" should get updated for the TTL of G&H under date 21 against Ahmed Kamal.
However this figure should be visible (or avaible for reference) there after for any day, like gets converted to text automatically so that when I revise the figures for 22 in G & H, I must know what was his sales for the day 21 or 20 or 19, etc. so that I can know the trend of sales happening with him.

is there any way, I can do this thought into the attached format file...

Please help & thanks

Sajith

Hi Sajith

Try the attached. I`ve added two modules SalesTracker and SetNewYear, SalesTracker contains the sub dailySalesTracker which copies the total of columns G and H for all sales execs to the matching rows on the score sheet tab taking leap years into account. This is currently called from the onChange event of the worksheet Main, overkill I know but it runs pretty quick and I couldn`t think of a better way to do it other than running it from a button on the sheet. SetNewYear contains sub resetScoreCard and does what it says it reformats the Score Sheet tab for the current year highlighting Fridays in grey taking into account leap years.
Rather than try to calculate column offsets they are looked up on a Data tab, currently hidden.

Both modules contain two lines of code at the start

currentYear = Now()
' currentYear = DateAdd("yyyy", -2, Now()) ' for test the last leap year

To test leap years comment out the first line and uncomment the second, run resetScoreCard first from the button on the tab, make sure both subs are inline or it will fall over. Obviously in the real world only the first line will be 'live'.

Just a suggestion I think the Score Card tab looks a bit odd with empty rows for Retail and SME if you grey them out with the same colour as the sub total rows it won`t affect the reformatting

Regards
Ian

Thank you Ianxwood! That did amazing help to me