I have a formula in a cell at the top of the worksheet, which calculates
values of numbers below it (formula = G3*G8). However, I am constantly
adding rows below this formula with data into row G8, and as a result, the
formula changes to =G3*G9 which is no longer correct. I have tried placing $
in front of both the G and the 8 however it does not keep the cell constant.
Is there any way I can keep that cell constant while still adding rows below
it? Thanks for your help.

Here is a little trick to get cell references that don't adjust. A formula
like:

=G8

will adjust as you add or remove rows above G8, but the formula:

=INDIRECT("G"&8)

refers to the same cell, but will not adjust as you add or remove cells!
--
Gary's Student
gsnu200703


"ChrisG" wrote:

I have a formula in a cell at the top of the worksheet, which calculates
values of numbers below it (formula = G3*G8). However, I am constantly
adding rows below this formula with data into row G8, and as a result, the
formula changes to =G3*G9 which is no longer correct. I have tried placing $
in front of both the G and the 8 however it does not keep the cell constant.
Is there any way I can keep that cell constant while still adding rows below
it? Thanks for your help.

OK, that makes sense, but how do I write the formula when I am trying to
multiply two numbers without having either cell change. I could not get G8
and G3 to multiply with "Indirect" writen outside of the brackets?

"Gary''s Student" wrote:

Here is a little trick to get cell references that don't adjust. A formula
like:

=G8

will adjust as you add or remove rows above G8, but the formula:

=INDIRECT("G"&8)

refers to the same cell, but will not adjust as you add or remove cells!
--
Gary's Student
gsnu200703


"ChrisG" wrote:

I have a formula in a cell at the top of the worksheet, which calculates
values of numbers below it (formula = G3*G8). However, I am constantly
adding rows below this formula with data into row G8, and as a result, the
formula changes to =G3*G9 which is no longer correct. I have tried placing $
in front of both the G and the 8 however it does not keep the cell constant.
Is there any way I can keep that cell constant while still adding rows below
it? Thanks for your help.

Try:

=INDIRECT("G"&8)*INDIRECT("G"&3)

--
Gary's Student
gsnu200703


"ChrisG" wrote:

OK, that makes sense, but how do I write the formula when I am trying to
multiply two numbers without having either cell change. I could not get G8
and G3 to multiply with "Indirect" writen outside of the brackets?

"Gary''s Student" wrote:

Here is a little trick to get cell references that don't adjust. A formula
like:

=G8

will adjust as you add or remove rows above G8, but the formula:

=INDIRECT("G"&8)

refers to the same cell, but will not adjust as you add or remove cells!
--
Gary's Student
gsnu200703


"ChrisG" wrote:

I have a formula in a cell at the top of the worksheet, which calculates
values of numbers below it (formula = G3*G8). However, I am constantly
adding rows below this formula with data into row G8, and as a result, the
formula changes to =G3*G9 which is no longer correct. I have tried placing $
in front of both the G and the 8 however it does not keep the cell constant.
Is there any way I can keep that cell constant while still adding rows below
it? Thanks for your help.