Forum Discussion
Stop Autofill from Incrementing Certain Cells
I am going to try to explain this the best I can. Please, be patient with me. It might take a few attempts when it comes to properly describing things. Thanks!
I am making a spreadsheet that keeps track of the ages for characters in a series I am writing. There are MANY characters, each one with a different birthday. Each story takes place for a different date and year. Right now, there are about 40 periods of time, and those are at the top of the spreadsheet.
So, I basically have a character name, their date of birth, and then the subsequent cells calculate how old the character is for that period. When I try to use the auto fill, it increments the cell so that it no longer uses the correct cell. In essence, it tries to use the cell next to the date of birth cell. It does the same thing when I try with the column as well.
So, how do I get the autofill to use a static cell horizontally while incrementing the cell vertically? Or the other way around?
Let's say the date of birth is in B2 and down. In the formulas to the right, refer to $B2 instead of just B2.
The $ before the column letter B makes the column reference absolute, i.e. fixed.
Because there is no $ before the row number 2, the row reference is relative, i.e. it changes when you fill down.
3 Replies
Thank you so much. That worked beautifully.
More information and examples are available in this article: Why use $ in Excel formula: relative & absolute cell reference (And mixed cell references used a fixed column and a varying row, or a varying column and a fixed row.)
Let's say the date of birth is in B2 and down. In the formulas to the right, refer to $B2 instead of just B2.
The $ before the column letter B makes the column reference absolute, i.e. fixed.
Because there is no $ before the row number 2, the row reference is relative, i.e. it changes when you fill down.
