I would like to convince you that using formulas to "get the value on every Monday" is not the best design. Specifically, why not "get" values (for all the weeks, over a long period) that are recalculated every time one of (most) workbook cells is edited? That would mean that you could show current, summarized data ... the latest, incomplete week included. (I'm not sure if a week, as you define it, is Monday through Sunday, or Tuesday through Monday; I wrote formulas for the latter.)
Besides, formulas are not a good way to make a "permanent" record of some value. Procedural code (VBA is used with Excel, in chunks that are usually called macros) is powerful, but raises potential security issues. Also, if you "lock in" the values for a week, you need to plan for how corrections to prior-week data will be handled.
So, I have attached a spreadsheet with some data for a fictional livestock auction company. The summary data is (nearly all) built via formulas as you can see in this screen capture:
But even with all those calculation-by-formula cells, there is only five basic formulas for all of those five columns, plus two Custom formats, IIRC. (And that includes the "bonus" column Pct Chg.)
The "Change from Prev. Week" formulas are simple (but note how they refer to data in two rows, not just one):
Starting from D4, the formula is:
=D4/C3
But starting from D7, to make cells in the column "empty" when a week has not started:
=IF( B7="", "", D7/C6 )
It's your call, but I like the latter.And the formulas for "Pct Chg" are simply:
Starting from E4, the formula is:
=D4/C3
But starting from E7, to make cells appear empty when the week has not started:
=IF( B7="", "", D7/C6 )I used a Custom format definition to get the leading plus signs displayed.
The "Amount" column formulas sum the "Cattle Sold" data - by week - from the Details worksheet; these are the advanced formulas:
Starting from cell D7:
=IF( B7="", "", SUMIFS( Details!B$3:B$1000, Details!A$3:A$1000, ">="&A7, Details!A$3:A$1000, "<"&A8 ) )Specifically, that formula sums the Cattle Sold values in Details row where the Date there is on or after the date in A7, but before the date in A8. The formulas handle data up through row 1000 on the Details worksheet (and that last row can be readily increased).
In the Details data, note that while I usually included one row per day, not all days need to be present (I left out Sundays), and you can include multiple rows per day (see rows for 10/21). The data rows are not required to be in a particular order, and there are no formulas on the Details page.
I could also make the latest, incomplete week text read "Week 8-to-date", but first I would want to find out which version of Excel you are using.
What's your opinion?