Each week I pull sales data and add to a report. On the top of the report, I have cell designated that the user can type in the week of the year, and the spreadsheet will update to reflect the data for that particular week. That part is easy, just use index and match formulas (thanks to this forum help a few years ago) However I also have a column that shows the YTD totals based on the week selected.
Currently, each week when I add in the sales data, I have to go in to each and establish the range for the YTD sum for each item.
For example: 5 items are sold, so week 1 the YTD range to sum from is rows 1-5, but for week two it would be rows 1-10, week three is rows 1-15, etc.
Is there a faster way to do this? So formula I could just copy down, instead of updating the parameters for each new week. Also if an item sells zero units in a week, it does not show up in the sales data, so for that particular week it would show a zero for the YTD totals, because it wasn't in that weeks sales data.
Any way to speed up this process?
Windows 10 PC, MS Excel 16