Automatic Cell Value Change

[HanSam]

hi,

I need help on the file attached.

Basically I have a cell (D2) that user can change the value using a spin control. (Just a set list of Months)

What I want to happen is whenever D2 changes, cells G2:R2 changes as well according to the value of D2. G2:R2 contains the last 12 months before the month of D2.

As on the file, the sample is December-16, and G2 is Nov-16, H2 is Oct-16 and so forth. So when D2 becomes November-16, G2 should now be Oct-16 and H2 is Sept-16.

 

[Hui]

G2: =EDATE($D$2,-13+COLUMNS($G$2:G2))
Copy across

 

[HanSam]

Thanks Hui!

 

[Hui]

My solution isn't what your question asked for, but I figure it's what you meant to ask for

 

[HanSam]

it's not exactly what I was looking for, but I went out and dissected the formula. I trimmed it down to just =EDATE(Ref Cell, -1) then just edited -1 to -2, -3 until -12. It worked perfect! I just can't drag the formula, it is just copying it, not making a series.

 

[NARAYANK991]

Hi ,

Wouldn't =EDATE($D$2,-COLUMNS($G$2:G2)) entered in G2 and copied across give you what you want ?

Narayan

 

[HanSam]

Hi Narayan, that's more what I wanted. Hui's answer had the months in reverse.