Re: How to fix a formula, so when a row is inserted it doesn't change?

[Earl Kiosterud]

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

 

[Guest]

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

 

[Guest]

I am doing something similar except I am inserting cells into a row of data.
But then I am sorting these rows based on the information that comes out of
the INDIRECT formula. It works fine using this method until I sort it. How
do I make the row reference relative but the column absolute? Again, I need
to use the INDIRECT funtion because I am inserting cells.

 

[pinmaster]

Try this:

=SUM(INDIRECT("A1:A3"))


HTH
JG

 

[Nick Bishop]

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 D35
.... 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.

 

[CJB]

Hi, I'm having the same sort of problem. My formula is
=sum(C6*C3+D6*D3+E6*E3) ... the C3, D3 etc keeps changing to C4 ... C5 etc as
I copy the formula down. C3, D3, E3 is the price of a product.

 

[Don Guillett]

Look in the help index for ABSOLUTE

 

[Dave Peterson]

You don't need the =sum() function:
=C6*$C$3+D6*$D$3+E6*$E$3

And I find using ()'s makes it easier to see what's going on:
=(C6*$C$3)+(D6*$D$3)+(E6*$E$3)

And excel has a formula designed for this kind of thing:
=sumproduct($c$3:$e$3,c6:e6)