Excel question - is there a formula that can automatically update based on the day of the week?

Question

http://i30.photobucket.com/albums/c326/Jennifer__B/SampleExcelWorksheet.jpg

Above is a link to a mock example spreadsheet. The column I am looking to fill is AJ5 - AJ26. I need to keep as close to this format as possible, as this is not my document to change. So, based on the information in the sample- on the 13th, I would like column AJ to sum days 1 thru 12. On the 14th I would like the column AJ to sum days 1 – 14, etc. Is there a formula for this to automatically update without having to manually change the formula daily? A solution to this problem would save my day!

If it helps, I already have a cell in the document which automatically updates to the current date. However, I wonder if by looking to link to the date I might be on the wrong path?

Answer 1

Well I tried doing this using an array function but I just couldn't get it to work because of the TOTAL columns at the end of each week.

=SUM(IF(C$4:AE$4
So I tried to remove the TOTAL columns by using an AND function but it seemed to exceed Excel's ability to use array functions. The best I could come up with is to create a hidden row beneath each row and use this formula.

=IF(C$4<=DAY(TODAY()),C5,0)

Start at cell C5, then c'n'p it into the cells C5:AE4. You'll need to delete the formula in each column that you don't want to add, namely the "Total" columns.

In the "To Date" column for Row 5, add this formula.

=SUM(C6:AE6)

It's not too much work as long as you don't have many analysts. Here are step-by-step instructions to get it done as quick as possible because the above is a little ambiguous.

1. Select Row 6, right-click, and select Insert Row.

2. Cut and Paste this formula into cell C5

=IF(AND(C$4<=DAY(TODAY()),NOT(ISBLANK(C$4))),C5,0)

3. Copy cell C5, highlight cells C6:AE6, select Paste Special, then select Formula and click OK.

4. Select cell AJ5. Enter this formula.

=SUM(C6:AE6)

5. Select Row 5 again and select Copy.

6. Start with the last row and select Insert Copied Cells. This will get a bit tedious depending on the number of analysts you need to do this for. Repeat from Step 5 until you have inserted rows for each analyst.

7. Select cells AJ5:AJ6. Right-click and select Copy.

8. Select all the rows with analysts and newly inserted rows.

9. Right-click and select Paste Special, then select Formula, then click OK.

10. Select each of the newly inserted rows by using Shift and Left mouse button. Right-click on any of the selected Row numbers and select Hide.

Sheesh, even this sounds confusing. Here's a hopefully small video that will show you what I did in case the above instructions confused you. It's a little over 5 MB in size. Hope you have a high-speed internet connection. This particular file hosting site has a high amount of traffic so it may take a couple of tries to download it. :P

http://www.sendspace.com/file/6dfhnj

I have to say, I have a newfound respect for the Video Professor. It took me about 20 takes to get that video right. :)

Answer 2

You can sum days that have no values since they won't add anything to your total. I think you mean that you only want a running total for the month. What I would do is insert a row at the top and set it to be: C1=MONTH(C5) D1=MONTH(D5) ... etc. Assuming you have currently have row 4 with dates, not just day numbers. Then I'd set the cells in AJ to be: =SUMIF(C$1:AW$1, MONTH ($A$2),C6:AE6) Starting at AJ6 and copying down. assuming that A2 is the cell with the current date.