Interpret value from formula as a formula in Excel 2010

Question

I have formula which I'm using to dynamically create formulas to reference data in separate spreadsheets.

For example, I have a formula in cell which returns =<path>\[<file>]<reference>. So the return value for cell R496 would be the string ='D:\Documents\[Filename.xls]sheet1'!$A$3.

According to Apple Pie's answer on Using the value in a cell as a cell reference in a formula, I can reference values from another cell in a formula my formula by using =INDIRECT(reference). Unfortunately, that doesn't seem to work when interpreting a full formula from another cell.

In Microsoft Excel 2013 and above, I would be able to use =FORMULATEXT(), but unfortunately that option is not available in Excel 2010.

How can I use the value from a cell as a formula in Microsoft Excel 2010?

Answer 1

To return a cell's formula rather than its value, use FORMULATEXT(). So if A1 contains:

=A2 + A4

then pick some cell and enter:

=FORMULATEXT(A1)

EDIT#1:

If you have Excel 2010, you could use the following UDF():

Public Function MyFormula(r As Range) As String
    MyFormula = r(1).Formula
End Function

EDIT#2:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Answer 2

Try this:

=INDIRECT("'" & LEFT(CELL("filename",A487),FIND("[",CELL("filename",A487))-1) & "[" & $C496 & " " & $B496 & ".xls" & "]" & "sheet1'!$H$3")

---

As you want a more general method to evaluate a formula. You will need to use this UDF:

Function MyEvaluate(rng as range)
    MyEvaluate = rng.parent.Evaluate(rng.value)
End Function

There is still no need for the = at the beginning, but will not hurt either.