Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
How to identify an inserted picture within a formula | Excel Discussion (Misc queries) |