creating variations of existing excel formula

[archanapatelwhite]

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..

 

[Bernie Deitrick]

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