Roll up data from weekly view to monthly

[scruz9]

Hi Everyone,

I have an attached file that represents an actual file of mine.

In my file, I use a cell in the summary file that lists YYWW. As an example, please adjust it to 1552 and you can see the cells in weekly view adjust.

Anyways, I'd like to create a monthly view that is also dynamic and knows at which current week we are at based off the cell in the summary file. I would love to get any ideas on how to do this best.

Thanks!

[FDibbins]

Can you show some samples answers please?

You could change B2 to this...
=B3
Custom Format to MMMM

Also, to get your yyww value, you could use this, instead of that big formula yopu have...
B4=--(TEXT(B3,"yy")&TEXT(WEEKNUM(B3)-1,"00"))
copied across

[scruz9]

Ah thank you for that tip!

I've attached a copy of what the month view would look like. With the proper formula, it would reference the summary tab and know to list all Months and Years up to 1602 which is January 2016.

[FDibbins]

Here is 1 way to do it, using a helper row (which you can hide if needed). I used row 5, you can use whatever you want, just adjust the references as needed.

B5=IF(B4<=Summary!$A$3,1+'Weekly View'!A5,'Weekly View'!A5)
copied across. This will create a list of cells to pull in

Then, wherever you want your answers...
=iferror(INDEX('Weekly View'!$B7:$BC7,MATCH(COLUMNS('Weekly View'!$A$1:A1),'Weekly View'!$B$5:$BC$5,0)),"")
copied across and down as needed