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!
1
votes
1 Answers
1
votes
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()))
