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

snd

New Member
Joined
Jan 24, 2010
Messages
34
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
have all your data ie every month in sheet2

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

rajat565

New Member
Joined
Mar 5, 2016
Messages
4
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

Board Regular
Joined
Apr 11, 2010
Messages
10,985
col F
row 701/04/201501/05/201501/06/201501/07/201501/08/201501/09/201501/10/201501/11/201501/12/201501/01/201601/02/201601/03/201601/04/2016
3456789876543
say we want only the last 3 months
which is feb mar april
col I
last date required =01/04/2016row 17
01/02/201601/03/201601/04/2016
543
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

New Member
Joined
Jan 24, 2010
Messages
34
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. :)
 

Forum statistics

Threads
1,081,676
Messages
5,360,441
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top