software

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.

quot;Earl Kiosterudquot; wrote:

gt; Matjaz,
gt;
gt; YOu're right, the absolute references are for copies only. They don't
gt; prevent cell references from changing when cells are moved. Use
gt;
gt; INDIRECT(quot;A1quot;) INDIRECT(quot;A2quot;) ...
gt; or
gt; =SUM(INDIRECT(quot;A1:A3quot;))
gt;
gt; These aren't cell references -- they're text. They won't change when the
gt; cell gets moved.
gt;
gt; Earl Kiosterud
gt; mvpearl omitthisword at verizon period net
gt; -------------------------------------------
gt;
gt; quot;Matjazquot; gt; wrote in message
gt; ...
gt; gt; I have a row with data added daily, newest on the top. Then I have a
gt; formula
gt; gt; which calculates some output according to the last three inserted cells
gt; (i.e.
gt; gt; =A1 A2 A3). But everytime I add data (insert a row) formula changes (i.e.
gt; to
gt; gt; =A2 A3 A4). I tried to surround formula with $ signs (i.e.
gt; =$A$1 $A$2 $A$3)
gt; gt; but it doesnt help (looks like absolute references only apply to copying
gt; gt; formulas).
gt; gt;
gt; gt; Is there something else I could try? Maybe something like =%A%1 %A%2 %A%3
gt; or
gt; gt; some other character?
gt;
gt;
gt;


Try this:

=SUM(INDIRECT(quot;A1:A3quot;))HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=496576Hi, 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.

quot;pinmasterquot; wrote:

gt;
gt; Try this:
gt;
gt; =SUM(INDIRECT(quot;A1:A3quot;))
gt;
gt;
gt; HTH
gt; JG
gt;
gt;
gt; --
gt; pinmaster
gt; ------------------------------------------------------------------------
gt; pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
gt; View this thread: www.excelforum.com/showthread...hreadid=496576
gt;
gt;

Look in the help index for ABSOLUTE

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

quot;CJBquot; gt; wrote in message
...
gt; Hi, I'm having the same sort of problem. My formula is
gt; =sum(C6*C3 D6*D3 E6*E3) ... the C3, D3 etc keeps changing to C4 ... C5 etc
gt; as
gt; I copy the formula down. C3, D3, E3 is the price of a product.
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUM(INDIRECT(quot;A1:A3quot;))
gt;gt;
gt;gt;
gt;gt; HTH
gt;gt; JG
gt;gt;
gt;gt;
gt;gt; --
gt;gt; pinmaster
gt;gt; ------------------------------------------------------------------------
gt;gt; pinmaster's Profile:
gt;gt; www.excelforum.com/member.php...foamp;userid=6261
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=496576
gt;gt;
gt;gt; 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:
gt;
gt; Hi, I'm having the same sort of problem. My formula is
gt; =sum(C6*C3 D6*D3 E6*E3) ... the C3, D3 etc keeps changing to C4 ... C5 etc as
gt; I copy the formula down. C3, D3, E3 is the price of a product.
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt; gt;
gt; gt; Try this:
gt; gt;
gt; gt; =SUM(INDIRECT(quot;A1:A3quot;))
gt; gt;
gt; gt;
gt; gt; HTH
gt; gt; JG
gt; gt;
gt; gt;
gt; gt; --
gt; gt; pinmaster
gt; gt; ------------------------------------------------------------------------
gt; gt; pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=496576
gt; gt;
gt; gt;

--

Dave Peterson