Formulas : An Excel formula to extract the contents of

  • Text


    I am looking for a formula to retrieve the contents of a formula in another workbook. For example, if cell A1 in workbook named A.xls contains a formula =2+2. Can anyone think of a formula to extract the whole formula from this workbook instead of the end result, ie 4?


    Your input will be appreciated.

    Digita

  • I think you may have checked "Solved" by accident. If you have actually solved this problem, please post your solution. I'd love to see it.


    If not solved, just UNCHECK "Solved" -- as thread author only you (or an OzGrid Admin) -- can do this. Seeing the "Solved" label on the thread will cause folks to ignore it.



    If you are willing to use VBA, then the following shows how to get the formula from cell A1 of Sheet1 of workbook "Book2" and write it into cell B3 of the active worksheet so that it displays as a formula (text) instead of a result.


    Code
    1. Sub test()
    2. Dim Form As String
    3. Form = Workbooks("Book2").Sheets("Sheet1").Range("A1").Formula
    4. Range("B3") = "'" & Form
    5. End Sub
  • Thank you for the reply by Tom who has provided a useful VBA code to extract the formula (not a result) from a cell in an external workbook. Now if I have about more than 250 cells in a column containing formulas that need to be extracted. I've tried to modify the code to the following & included an array to complete the task but had no success.


    Option Explicit
    Sub test()
    form As Range
    form = Workbooks("Book2").Sheets("Sheet1").Range("E2:E4").Formula
    Range("B3").FormulaArray = form
    End Sub



    Is there a way to extract them all in one hit? Can someone help please?


    Thank you for your input.


    Digita

    Digita

  • Do you want them extracted into separate cells in the other workbook...


    i.e. the formulas in book1 A1:A250 get copied into Book2 A1:A250 as formulas as opposed to values ?


    If that's the case, something like this clunky Loop would suffice,



    Hope this helps,


    Will