Forcing a static reference in a formula

[Bevelmon]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

I'm have a cell A that has a simple formula for to take cell B and subtract cell C. however I insert a row at cell B which cell A now reference as cell B+1. What I want to do is have cell A (the formula) maintain a static reference to cell B (no +1) as I insert lines.

Is there anyway to do this by a reference or do I need to create a macro and do all of it manually?

Thanks in advance.
B.

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

I'm have a cell A that has a simple formula for to take cell B and subtract
cell C. however I insert a row at cell B which cell A now reference as cell
B+1. What I want to do is have cell A (the formula) maintain a static
reference to cell B (no +1) as I insert lines.

Is there anyway to do this by a reference or do I need to create a macro and
do all of it manually?

Thanks in advance.
B.

Use an absolute reference. For example =b$1+c$1 if you want the columns to
be relative , or =$b$1+$c$1 for an absolute reference.

 

[moofmoof]

I have the same question as Bevelmon. Unfortunately absolute references only seem to protect your formula when filling down, right, etc, not when you insert a new row or column of data. Here's my situation: Every week I'm adding a new column of data. I'm calculating the percent change from the previous week's data. As soon as I go to insert my new column, the formulas in my percent change column all change to reflect where the data has been moved, not the cells that I want to calculate on. (I'm inserting my cells to the left of the ones that already exist so the oldest data rolls off to the right). Admittedly, I can fix this by changing the first cell and filling down again, but that seems like a lot of work to redo something that was there before I inserted some cells.

 

[Bob Greenblatt]

I have the same question as Bevelmon. Unfortunately absolute references only
seem to protect your formula when filling down, right, etc, not when you
insert a new row or column of data. Here's my situation: Every week I'm adding
a new column of data. I'm calculating the percent change from the previous
week's data. As soon as I go to insert my new column, the formulas in my
percent change column all change to reflect where the data has been moved, not
the cells that I want to calculate on. (I'm inserting my cells to the left of
the ones that already exist so the oldest data rolls off to the right).
Admittedly, I can fix this by changing the first cell and filling down again,
but that seems like a lot of work to redo something that was there before I
inserted some cells.

Do you realize that cell references can also be a combination of static and
absolute? References like a$1, or $a1 may be what you want.

 

[Bevelmon]

Moofmoof, A better explanation of the problem, thanks for that! I do believe it doesn't matter whether the cells are above and to the left or not. If you insert and if moves the Cell location the formulas follow that Cell to the new location.

What we are looking for is a way to hard code the cell reference in a formula to remain 'C7' regardless if i insert 10 rows above row 7.

Seems like there has to be a way, I just can't figure out what it is.
Hopefully it can be done without writing VB code, I have several sheets where this is very useful.

Still looking for an answer,
Bevelmon

 

[Bob Greenblatt]

Moofmoof, A better explanation of the problem, thanks for that! I do believe
it doesn't matter whether the cells are above and to the left or not. If you
insert and if moves the Cell location the formulas follow that Cell to the new
location.

What we are looking for is a way to hard code the cell reference in a formula
to remain 'C7' regardless if i insert 10 rows above row 7.

Seems like there has to be a way, I just can't figure out what it is.
Hopefully it can be done without writing VB code, I have several sheets where
this is very useful.

Still looking for an answer,
Bevelmon

Your best bet is to define a name that refers to the text "C7". Then use an
indirect reference. Otherwise, as you discovered, Excel will always try to
update the reference to the specific cell you identified.

 

[Lavanya Ramadoss]

is there a formula that can convert a text into a cell reference per
your note? Like you give it a static input of what the clel should be
like and the formula converts it to a cell reference? This would
really help my business if you could help. Thank you. I hope also
that this doesn't involve VBA...!

 

[Laroche J]

Lavanya Ramadoss wrote on 2009-03-13 15:36:

is there a formula that can convert a text into a cell reference per
your note? Like you give it a static input of what the clel should be
like and the formula converts it to a cell reference? This would
really help my business if you could help. Thank you. I hope also
that this doesn't involve VBA...!

Do you mean getting in cell A2 the content of the cell whose address is in
A1? If so, use =indirect(A1) in cell A2. Then if you type D5 in cell A1, the
content of A2 will the the content of D5.

JL
Mac OS X 10.4.11, Office v.X 10.1.9