Interpret value from formula as a formula in Excel 2010

0
votes

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.

Example of formula

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.

Attempting to use =INDIRECT()

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

=INDIRECT() not available

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

2
excelexcel-formulaexcel-2010
As this is just a reference to one cell, remove the = in your formula, then use INDIRECT. – Scott Craner
What is the formula to get R496 – Scott Craner
@ScottCraner It's split into multiple parts. Get current file path with =LEFT(CELL("filename",A487),FIND("[",CELL("filename",A487))-1). Calculate file name with =$C496 & " " & $B496 & ".xls". Final formula reference in R496 is =("='" & $P496 & "[" & $Q496 & "]" & "sheet1'!$H$3") – Stevoisiak
That is not what I asked. What is the exact formula in R496 – Scott Craner
@ScottCraner Just edited my comment. The formula is unfortunately fairly complex – Stevoisiak

2 Answers

1
votes

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

enter image description here

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

1
votes

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)

enter image description here

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!