How do I drag down complex calculations in open office.

Question

For example if A1 contains 30 and in A2 I write =A1-6 then I'll get 27 in A2 and if I click the little black squair in the lower right hand corner of A2 and drag it down then I get a column that looks like this:

30
27
24
21
18
15
12

And so on.

If I try to do this with a formula of multiple variables though then it won't work. For example if I instead type in A2 = (A1 - B1)*C1 and drag that down in the same way then the next cell will have the formula = (A2 - B2)*C2. But I only want the A# to get incremented.

How do I do this?

Answer 1

The "$" before a letter(column) or number (row) will prevent it from auto incrementing. "$a$2" will never increment at all. "a$2" the column only increments. "$a2" the row only increments

type in A2 = (A1 - B1)*C1
 I only want the A# to get incremented
        a2 = ($a1-$b$1)*$c$1

Answer 2

Here is a Macro for you:

REM  *****  BASIC  *****

Sub Main


End Sub


sub arg
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = "30"


dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
dim args4(0) as new com.sun.star.beans.PropertyValue

for a=0 to 100
args1(0).Name="ToPoint"
args1(0).Value="a"+(a+4)

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())

rem ----------------------------------------------------------------------

args4(0).Name = "StringName"
args4(0).Value = "="&30-(3*a)


dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())
next a
rem ----------------------------------------------------------------------

end sub