0

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?

Stevoisiak
  • 23,794
  • 27
  • 122
  • 225
  • As this is just a reference to one cell, remove the `=` in your formula, then use INDIRECT. – Scott Craner Jul 10 '17 at 15:43
  • What is the formula to get R496 – Scott Craner Jul 10 '17 at 15:47
  • @ScottCraner It's split into multiple parts. Get [current file path](https://superuser.com/q/1226805/358766) 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 Jul 10 '17 at 15:49
  • That is not what I asked. What is the exact formula in `R496` – Scott Craner Jul 10 '17 at 15:50
  • @ScottCraner Just edited my comment. The formula is unfortunately fairly complex – Stevoisiak Jul 10 '17 at 15:51
  • `=INDIRECT("'" & $P496 & "[" & $Q496 & "]" & "sheet1'!$H$3")` You need to remove the `=` in the string – Scott Craner Jul 10 '17 at 15:52
  • You then can substitute each cell reference in the string with the formula found in each cell: `=INDIRECT("'" & LEFT(CELL("filename",A487),FIND("[",CELL("filename",A487))-‌​1) & "[" & $C496 & " " & $B496 & ".xls" & "]" & "sheet1'!$H$3")` – Scott Craner Jul 10 '17 at 15:53
  • @ScottCraner Indirect is giving me "Invalid cell reference" errors, even with simple formulas like `=INDIRECT(8*8)` – Stevoisiak Jul 10 '17 at 15:57
  • `8*8` is not a cell reference `A1` is a cell reference. As I said in my first comment, Your formula is a single cell reference and therefore will work. INDIRECT will not take a string that looks like a formula and evaluate it. You will need VBA for that. INDIRECT will take a string that looks like cell references and make them that. – Scott Craner Jul 10 '17 at 16:00
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/148802/discussion-between-steven-vascellaro-and-scott-craner). – Stevoisiak Jul 10 '17 at 16:01
  • The comments added unseen characters. Try my answer below. – Scott Craner Jul 10 '17 at 16:05

2 Answers2

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)

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!

Community
  • 1
  • 1
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
1

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Can you explain how this formula is meant to work? It's not functioning properly for me – Stevoisiak Jul 10 '17 at 16:07
  • It is the concatenated version of all your cells. it should create the string: `'D:\Documents\[Filename.xls]sheet1'!$A$3` and then return the value in that cell as INDIRECT turns strings that look like references into actual references. For example `=INDIRECT("B" & 2)` will return the value in `B2` – Scott Craner Jul 10 '17 at 16:10