Formula staying constant when copying into new cells, keeps going to the right

Occasional Visitor

Formula staying constant when copying into new cells, keeps going to the right

I am trying to create a budget excel sheet for my fiancée and I, the formula I am trying to copy a formula exactly from the source without it making the cells move. I know this can be accomplished; however, I do not know how. The formula I have now calls on cells x5 and aa5. the formula is as follows =PRODUCT(X5*AA5), this is what need to stay constant. I also have had it written as =X5*AA5. However, i have a problem where it keeps moving the cells right when I try to copy move it, this is what it does with one cell movement, =PRODUCT(Y5*AB5), this is what I need to stop as I have many cells that need the =X5*AA5 formula. Things I have tried, =X\$5\$*A\$A\$5\$, ctrl + 1, f4. nothing is working for me though so here I am. any help on this is appreciated.

Thank you

2 Replies

Re: Formula staying constant when copying into new cells, keeps going to the right

You almost got it:  =\$X\$5*\$AA\$5 instead of =X\$5\$*A\$A\$5\$.

PS....  If you type =X5*AA5, then select X5*AA5 in the Formula Bar and press f4 repeatedly, it should cycle through various forms of absolute references automagically.

PPS....  Usually, we can press f4 repeatedly immediately after typing X5 and again after typing AA5 as we enter the formula.  But sometimes that does not work, even in my version (Excel 2010).

Re: Formula staying constant when copying into new cells, keeps going to the right

Hi Joe

I read your answer with interest as we have the same problem in our sheet. We are calculating holiday on a weekly basis, each week needing the new "last weeks pay" to be inserted at the start and calculated for the growing year, so we only need the start column to be fixed. At the moment the formula reads =SUM(\$B\$17:BG17)/52 but as Scorpion's issue, it moves to the right and next week will be =SUM(\$C\$17:BF17)/52. Totally foxed so if you or anyone has any thoughts please can you help? It's so tedious manually changing every line. TIA