How to refer to a cell that its column keeps changing?

[snd]

Excel 2007

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​	Mar-15​	Apr-15​	May-15​	Jun-15​	Jul-15​	Aug-15​	Sep-15​	Oct-15​	Nov-15​	Dec-15​	Jan-16​	Feb-16​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2007

Row\Col	A​
3​	The current accounting month (Feb-16) is the last column, plus the previous 11 months, so my table always shows a year.

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2007

Row\Col	A​
5​	What happens every month, I hide the 1st month of the 11 months so as to keep the total months equals to12;

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2007

Row\Col	A​
6​	let’s see the next accounting month (Mar-16):

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2007

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
7​	Apr-15​	May-15​	Jun-15​	Jul-15​	Aug-15​	Sep-15​	Oct-15​	Nov-15​	Dec-15​	Jan-16​	Feb-16​	Mar-16​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2007

Row\Col	A​
8​	(I'll hide Mar-15 column in the actual sheet), and the new table will look like (Keep assuming Row number is still 1):

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2007

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
10​	Column	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​
11​	Row 1	Mar-15​	Apr-15​	May-15​	Jun-15​	Jul-15​	Aug-15​	Sep-15​	Oct-15​	Nov-15​	Dec-15​	Jan-16​	Feb-16​	Mar-16​
12​		Colmun A1 to be hidden

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2007

Row\Col	A​
14​	I want a formula that will refer to the first month of the first 11 months. (in the 1st table, it's Mar-15 [Cell A1], the 2nd table it's Apr-15 [Cell B1).
15​	When I reach accounting month Apr-16, columns A and B will be hidden; formula'll refer to cell C1 (May-15).
16​	The formula- I guess- should be like the indirect function, either gives the refrence of the cell or shows the contents (doesn't matter).
17​	I know it's simple, but I admit it, I'm no good in Excel, Please HELP.

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[oldbrewer]

have all your data ie every month in sheet2

in sheet1 have formula that retrieve only the 12 months to today's date ???

 

[rajat565]

Hello,

If you are trying to do a sum of the latest 12 months
you can use this formula

=SUM(INDEX(A1:z1,,COUNTA(A1:z1)-11):INDEX(A1:z1,,COUNTA(A1:z1)))

Where A1:z1 is the array where the months are mentioned in your template. As soon as you add values in the latest month, it would take the sum of last 12 months.
This formula assumes that your future months remain blank till the time you want to show some values there.

 

[oldbrewer]

col F

row 7

01/04/2015

01/05/2015

01/06/2015

01/07/2015

01/08/2015

01/09/2015

01/10/2015

01/11/2015

01/12/2015

01/01/2016

01/02/2016

01/03/2016

01/04/2016

3

4

5

6

7

8

9

8

7

6

5

4

3

say we want only the last 3 months

which is feb mar april

col I

last date required =

01/04/2016

row 17

01/02/2016

01/03/2016

01/04/2016

5

4

3

formula giving 1/4/2016

=OFFSET($E$7,0,MATCH($I$17,$F$7:$R$7,0))

formula giving 3 (under 1/4/2016)

=OFFSET($E$7,1,MATCH($I$17,$F$7:$R$7,0))

clearly the last 0 is -1 and -2 for the other 2 months

<colgroup><col span="5"><col span="2"><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[snd]

Thanks guys, oldbrewer and rajat565, for trying to help. I don't need to sum or any thing, all I need is just a reference to the cell of the 1st month of the 12 months, a cell reference or its contents (i.e., schedule1, accounting month is Feb-16, so the formula should spits out A1 or its contents (Mar-15). For accounting month Mar-16, the formula should refer to cell B1 (or its contents Apr-15), and so on.
Thanks again.