Find last 12 values based on week number/year

Good afternoon all,

Sorry this is a somewhat lazy question to ask since i know ive done this before (many years ago). But i am trying to fill a table of the last 12 values for the purposes of creating dynamic charts. I remember last time i used named ranges, offsets etc etc but been too long to remember how.

Ive attached a worksheet to explain it better.

*Edit* I should probably mention, i want to be able to change cells C1 and C2 to update the values. Everything else wil be rather static.

Any help would be greatly appreciated.

Cheers,

Pytheus

Hi Mark,

I've used the OFFSET and MATCH functions. Note that there are formulas in row 14 (above the table), do not remove them - I've colored them in white - These formulas are used by the formulas in the table.

Refer enclosed workbook. Is this something you can work with?

=INDEX($C$5:$BM$9,ROWS(C$15:C15),MATCH($C$2,$C$5:$BM$5,0)-12+COLUMNS($C15:C15))
Enter the above formula in C15 and copy & Paste across

Originally Posted by nflsales

=INDEX($C$5:$BM$9,ROWS(C$15:C15),MATCH($C$2,$C$5:$BM$5,0)-12+COLUMNS($C15:C15))
Enter the above formula in C15 and copy & Paste across

Hi Siva,

It seems your formula doesn't check the combination of week number and the year together.
For e.g., when VMC Week Number (C2) contains 52, it returns values only from column M onwards (for the past 12 months), it doesn't distinguish between column M and BM.