1

Is there a way to increment the column value in a reference to a cell in a formula? To give an example, in pseudo code, something like =CELL(Row, Column+Month("")) So, in January, the formula would reference A21, February A22, March A23 and so on. Thanks in advance!

user3713442
  • 478
  • 8
  • 22

1 Answers1

1

If say your fifth column (E) represents January, sixth February etc, then in ColumnE and dragged across to the right (assuming LTR set up):

=indirect("A"&Column()+16)

will return the contents of A21, then A22, and A23, etc.

The column for the source is fixed by your definition as A, so it is just a matter of supplying the variable row number - as the current column number (5) plus the number to top that up (ie +16) as far as required (ie to reach 21) for the first row containing the source for January. As the formula is copied across to the right the column number increments by one and hence the row referred to by the formula by one also.

Exactly the above principle applies if to increment by time (eg month number) rather than by copying across:

=indirect("A"&20+month(Today()))
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thanks. I think I should have made myself clearer. I want the formula to be in a single cell. So, say the formula is in J42 In January, J42 will reference A5 In February, J42 will reference A6 In March, J42 will reference A7 etc. – user3713442 Dec 22 '18 at 12:40
  • I'm not 100% clear on your formula. It seems to say that the column is fixed, but it is the row that is fixed. – user3713442 Dec 22 '18 at 12:44
  • Your edit means that the cell reference is A21,A22,A23,A24 etc. But I want the opposite: eg J21, I21, J21, K21 etc. Its the column that changes not the row. – user3713442 Dec 22 '18 at 12:49
  • @user3713442 pnuts solved your question and deserves to be accepted ('in January, the formula would reference A21, February A22, March A23 and so on'). If you want to reference columns rather than rows, try this: `=indirect(SUBSTITUTE(ADDRESS(1,COLUMN()-1+month(Today()),4), "1", "")&21)`. Put this in Cell J42; in January it will reference cell J21, Feb=K21, March=L21 and so on. Is that what you wanted? Credit [Ondra Žižka](https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter/30777592#30777592) for conversion of column number to a letter). – Tedinoz Jan 02 '19 at 22:44