Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I control the light/dark variations Excel 2007 color theme? | Excel Discussion (Misc queries) | |||
Creating a new excel file from an existing file using macro | Excel Programming | |||
Creating a Microsoft Words document from an existing Excel spreads | New Users to Excel | |||
Creating templates from existing forms | Excel Discussion (Misc queries) | |||
Creating new worksheet from existing | Excel Discussion (Misc queries) |