Named Dynamic Range Based on Current Month

[3G]

Hi there-

I'm trying to create a dynamic named range that will automatically update based on the current date (mainly the month). The list is in a row, not columns.


Here's what I have:

= OFFSET($D$2,0,1,1,COLUMN(D15)-COLUMN(MONTH(TODAY())


What I want is the range of months to shrink as the months progress in time. This is to roll from a projected value to an actual value as the months end.


Of course, it's not working.


Any thoughts?


Thanks!

3G

 

[Hui]

It will be something like

= OFFSET($D$2,0,COLUMN(D15),1,COLUMN(MONTH(TODAY()-COLUMN(D15))

or

= OFFSET($D$2,0,COLUMN(D15),1,COLUMN(MONTH(TODAY()-COLUMN(D15)+1)

 

[3G]

Thanks Hui! Seems like I was at least close!

 

[NARAYANK991]

Hi ,


I don't fully understand what you have in D15 ; since the MONTH function will return a value between 1 and 12 , the range will shrink as time passes only if the end date is a date in December ; any date in January will return 1 , which when subtracted will result in a greater value.


I assume you have some data in the columns beyond D , such as E2 , F2 ,...


Assuming you have a number in D15 , which is greater than 12 , say 15 , then a formula like :


=OFFSET($D$2,0,0,1,$D$15-MONTH(TODAY())


will extend from D2 through Q2 , when we are in January ; when we are in December , the range will shrink to D2:F2.


If you want the shrinking to happen in the other direction , change the above formula to :


=OFFSET($B$2,0,$D$15,1,MONTH(TODAY())-$D$15)


Narayan