How to fix a formula, so when a row is inserted it doesn't change? in Excel General Questions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Matjaz 9/14/2004 8:14 AM PST
  Question
  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?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
RagDyeR 9/14/2004 8:42 AM PST
  Answer
  Try this:

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

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Matjaz" <Matjaz@discussions.microsoft.com> wrote in message
news:99DF0347-3939-482B-9A4F-BEC3B211B90B@microsoft.com...
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?


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Earl Kiosterud 9/14/2004 8:43 AM PST
  Answer
  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" <Matjaz@discussions.microsoft.com> wrote in message
news:99DF0347-3939-482B-9A4F-BEC3B211B90B@microsoft.com...
> 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?


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Matjaz 9/14/2004 9:21 AM PST
   
  Thank you both very much. I knew there must be a way :)
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
futureplus 8/24/2005 6:50 PM PST
   
  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" <Matjaz@discussions.microsoft.com> wrote in message
> news:99DF0347-3939-482B-9A4F-BEC3B211B90B@microsoft.com...
> > 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?
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Nick Bishop 8/11/2008 7:44 PM PST
   
  "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
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Paul Cahoon 12/28/2005 7:36 PM PST
   
  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" <Matjaz@discussions.microsoft.com> wrote in message
> news:99DF0347-3939-482B-9A4F-BEC3B211B90B@microsoft.com...
> > 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?
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
pinmaster 12/29/2005 7:45 AM PST
   
  Try this:

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


HTH
JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View this thread: http://www.excelforum.com/showthread.php?threadid=496576
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
CJB 12/9/2008 1:31 PM PST
   
  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?action=getinfo&userid=6261
> View this thread: http://www.excelforum.com/showthread.php?threadid=496576
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Don Guillett 12/9/2008 1:51 PM PST
   
  Look in the help index for ABSOLUTE

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"CJB" <CJB@discussions.microsoft.com> wrote in message
news:ED6B159F-257F-45B2-8380-D6E0A8DA9068@microsoft.com...
> 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?action=getinfo&userid=6261
>> View this thread:
>> http://www.excelforum.com/showthread.php?threadid=496576
>>
>>

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 12/9/2008 1:53 PM PST
   
  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?action=getinfo&userid=6261
> > View this thread: http://www.excelforum.com/showthread.php?threadid=496576
> >
> >

--

Dave Peterson
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies