Return Formula, not Value

    Hello all.


    I have created formulas in cells BB2 through BM2 (1-12). I would like to copy the formula to B3 based on the content of A1 (A1 can only be a number from 1-12, representing months).


    Example, if A1=3, return the formula in BE2


    Formula in BE2 =


    =INDEX('1'!$B$2:$AR$2999,MATCH(INDIRECT("$A"&ROW()),'1'!$A$2:$A$2999,0),MATCH(INDIRECT(CHAR(COLUMN()+64)&"1"),'1'!$B$1:$AZ$1,0))


    thanks,
    Adrian.


    PS. I have been a reader of the forum for quite some time now. Decided to post today, after not being able to find the answer for the questions above. Thanks for the years of problem solving help.

    Re: Return Formula, not Value


    You could use a VB worksheet macro such as:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Row = 1 And Target.Column = 1 Then
  Range("B3").Value = "'" & Range("BA2").Offset(0, Range("A1").Value).Formula
End If


End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login