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

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.

"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?

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

Try this:

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


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496576

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

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.

"pinmaster" wrote:


Try this:

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


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496576

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

Look in the help index for ABSOLUTE

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CJB" wrote in message
...
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.

"pinmaster" wrote:


Try this:

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


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile:
http://www.excelforum.com/member.php...fo&userid=6261
View this thread:
http://www.excelforum.com/showthread...hreadid=496576

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

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)



CJB wrote:

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.

"pinmaster" wrote:


Try this:

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


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496576



--

Dave Peterson