creating variations of existing excel formula
 

hi can anyone help
I am trying to create a variation of a formula in one cell in another
ie formula in L13 = DATA!$M$1035 and L14 = =DATA!$M$1030 i want W13 to
be = =DATA!$M$1039 and W14 to = DATA!$M$1034 and so on..
so the formula in W13 will be the formula in L13 + 4 rows and the
formula on W14 to L14 + 4 rows..., I want to be able to copy it down..
I have created this function:

Function GetFormula(Cell As Range) As String
Dim Row As Integer
Dim NewRow As Integer
GetFormula = Cell.Formula
Row = Right(Right(GetFormula, Len(GetFormula) - InStr(GetFormula,
"$")), Len(Right(GetFormula, Len(GetFormula) - InStr(GetFormula,
"$"))) - InStr(Right(GetFormula, Len(GetFormula) - InStr(GetFormula,
"$")), "$"))
NewRow = Row + 4
GetFormula = Replace(GetFormula, Row, NewRow)
GetFormula = Right(GetFormula, Len(GetFormula) - 1)
UseFormula (GetFormula)
End Function

but this only displays the text of the formula in the cell not the
value..
please help..

creating variations of existing excel formula
 

A function won't work.

Better would be to use the INDEX function, keyed to two cells: one for the base row, and one for
the increment.

For example, put 1035 into cell A1, 0 into cell A13, -5 into cell A14, 0 into cell L1, and a 4 into
cell W1. Then use this in L13

=INDEX(Data!$M:$M, $A$1 + L$1 + $A13)

and copy it to L14, and then copy both cells to W13:W14. You can extend this as far as you want,
inserting new values for the offset into column A...


HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
hi can anyone help
I am trying to create a variation of a formula in one cell in another
ie formula in L13 = DATA!$M$1035 and L14 = =DATA!$M$1030 i want W13 to
be = =DATA!$M$1039 and W14 to = DATA!$M$1034 and so on..
so the formula in W13 will be the formula in L13 + 4 rows and the
formula on W14 to L14 + 4 rows..., I want to be able to copy it down..
I have created this function:

Function GetFormula(Cell As Range) As String
Dim Row As Integer
Dim NewRow As Integer
GetFormula = Cell.Formula
Row = Right(Right(GetFormula, Len(GetFormula) - InStr(GetFormula,
"$")), Len(Right(GetFormula, Len(GetFormula) - InStr(GetFormula,
"$"))) - InStr(Right(GetFormula, Len(GetFormula) - InStr(GetFormula,
"$")), "$"))
NewRow = Row + 4
GetFormula = Replace(GetFormula, Row, NewRow)
GetFormula = Right(GetFormula, Len(GetFormula) - 1)
UseFormula (GetFormula)
End Function

but this only displays the text of the formula in the cell not the
value..
please help..