Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to fix a formula, so when a row is inserted it doesn't cha
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" 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
|
|||
|
|||
Formulas fixed against cell move, but adjusting when copied
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |