How to fix a formula, so when a row is inserted it doesn't cha
 

Earl

this was very useful to me too. I need to use INDIRECT("B49")-INDIRECT("A49")
and then copy this formula down over 300 rows so the next row would read
INDIRECT("B50")-INDIRECT("A50"). When I try to copy the formula it doesnt
update to the new row but remains using INDIRECT("B49")-INDIRECT("A49")

Yor assistance would be greatly appreciated


"Earl Kiosterud" wrote:

Matjaz,

YOu're right, the absolute references are for copies only. They don't
prevent cell references from changing when cells are moved. Use

INDIRECT("A1") + INDIRECT("A2") ...
or
=SUM(INDIRECT("A1:A3"))

These aren't cell references -- they're text. They won't change when the
cell gets moved.

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Matjaz" wrote in message
...
I have a row with data added daily, newest on the top. Then I have a
formula
which calculates some output according to the last three inserted cells
(i.e.
=A1+A2+A3). But everytime I add data (insert a row) formula changes (i.e.
to
=A2+A3+A4). I tried to surround formula with $ signs (i.e.
=$A$1+$A$2+$A$3)
but it doesnt help (looks like absolute references only apply to copying
formulas).

Is there something else I could try? Maybe something like =%A%1+%A%2+%A%3
or
some other character?

Formulas fixed against cell move, but adjusting when copied
 

"futureplus" wrote:
this was very useful to me too. I need to use INDIRECT("B49")-INDIRECT("A49")
and then copy this formula down over 300 rows so the next row would read
INDIRECT("B50")-INDIRECT("A50"). When I try to copy the formula it doesnt
update to the new row but remains using INDIRECT("B49")-INDIRECT("A49")

The way to do it is a little involved, so I'll do this by example:
In column A type a header and some data: "Time", 45, 23, 89, 12.
In column B type a header and some data: "Time2", 48, 27, 94, 18.
In column D type a number, then a formula: 1, =D1+1
.... and copy D2 into D3:D5
.... this should result in column D being populated with the row number
In column E type a formula: =concatenate("A", D1)
.... and copy E1 into E2:E5
In column F type a formula: =concatenate("B", D1)
.... and copy F1 into F2:F5
.... this should result in column E and F being populated with things like
"A1", "B1", ...

Now for the bit we wanted to do all along
In column C type a header and formula: "Difference",
=indirect(F2)-indirect(E2)
.... and copy C2 into C3:C5
.... this should result in the values being the differences between columns B
and A (3, 4, 5, 6).
.... if you check C3, its formula should read: =indirect(F3)-indirect(E3)

Now to check that it still works OK when we move cells around ...
In cell A2, Insert - Cells - Move Cells Down, then in the blank A2 cell,
type the number 22.
.... Column A should now read: "Time", 22, 45, 23, 89, 12.
.... Column C should now read: "Difference", 26, -18, 71, -71

We've achieved our two objectives:
* a formula (in Column C) that doesn't change what cell is being referenced
(during cell moving or Insert - Cells)
* but the formula adjusts itself when being copied.

Tidying up:
You can hide columns D and E so they don't clutter the spreadsheet.

--
Nick Bishop
Allergic to Windows