I have months, Jan 2017, Feb 2017, Mar 2017 in a row. For the fourth entry, for Apr 2017, I am using the following formula
=DATE(YEAR(K9),MONTH(K9)+1,DAY(K9))
Where K9 is the cell reference for Mar 2017.
My problem comes in when I insert a column between the cells for Mar 2017 and Apr 2017. The formula continues to point to cell K9, but I want it to know point to cell L9, which is the newly created cell.
The reason for this is because I will manually type in Apr 2017 into the newly created cell, and I want my formula to now display, "May 2017"
How can I make my formula be dynamic?
Thanks
you stated adding a column, if that is the case then use:
=DATE(YEAR(INDEX(9:9,COLUMN()-1)),MONTH(INDEX(9:9,COLUMN()-1)),DAY(INDEX(9:9,COLUMN()-1)))
This will always refer to the column to the left. It will error if put in Column A.
If you are adding a row and you want to refer to the row above use:
=DATE(YEAR(INDEX(K:K,ROW()-1)),MONTH(INDEX(K:K,ROW()-1)),DAY(INDEX(K:K,ROW()-1)))
This will always refer to the row above. It will error if put in row 1.
This is a non volatile function.