Cell reference when inserting new column

0
votes

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

1
excelexcel-formulareferencecell
When you insert a column, you want the row to update one down? How does it follow that inserting a column creates a new row? – BruceWayne
Do you actually mean L9 rather than K10? – Stuart Allen
yes, I meant K10 – Y123

1 Answers

0
votes

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.