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
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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..
>
|
Similar Threads
