cell reference

[suffix]

hi @all!

i'm using excel 2003 on win2k.

i'll explain my problem by an example:
- write "1" in cell A1
- write "=A1" in B1
- select A1, "insert cells".
- now "1" can be found at A2

and here's the problem: the formula in B1 now says "=A2". how can
prevent excel from changing B1 to "=A2"? it should remain at "=A1"

???

i think i can't use the OFFSET-function, because i need to point to
cell in an other table (=Table2!A1 for example)

thanks in advance

 

[Mark]

You are using relative referencing when you use an address like A1. If
you use an absolute reference IE $A$1 then whenever that cell formula
is reproduced it will stay as $A$1 and not increment on you.

 

[Ragdyer]

Try this:

=INDIRECT("A1")