I am building a project profitability analysis model by week, that I then want to summarize by month. Some weeks overlap months (eg week 22 has 4 days in May, 1 in June). I have the number of weeks and the corresponding month in columns. For week 22, I show it as 22(1) and the cell to the right May, and 22(2) with cell to the right June.
I want to show the margin for each week going across in a separate worksheet, and then show the margin by month in another one.
If a project starts in week 20, what formula can I use to show the next week to be 21, then 22(1), then 22(2), 23 etc? I've tried offset, but that only works when the reference cell is static. In this case, projects can start any week of the year.