Results 1 to 5 of 5

20040715, 11:19 #1
 Join Date
 Mar 2001
 Posts
 989
 Thanks
 0
 Thanked 0 Times in 0 Posts
How can I 'read' a formula? (2000)
How can I refer to a cell that contains a formula, and extract the worksheet reference that the linked formula contains?? For example, if A1 contains '=Sheet1!D4' how can I get the word Sheet1 to display in cell A2? Andy.

20040715, 11:30 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: How can I 'read' a formula? (2000)
Formulas can be very complex, so I wonder what the use of this is. Not all formulas are of the form =SheetName!CellReference.
You can use a custom function to return the formula of a cell:
Public Function GetFormula(oCell As Range) As String
If oCell.HasFormula Then
GetFormula = oCell.Formula
End If
End Function
Say that cell A1 contains the formula =Sheet1!D4.
In cell A2, enter the formula =GetFormula(A1). A2 will display the formula as text.
In cell A3, you can enter a formula to extract the sheet name. A crude version is
=MID(A2,2,FIND("!",A2)2)
This could be refined to catch errors (for example if A1 does not contain a formula, or the formula does not refer to another cell, or if the sheet name contains spaces, ...

20040715, 11:32 #3
 Join Date
 Mar 2001
 Posts
 989
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How can I 'read' a formula? (2000)
I was hoping that this could be done directly in Excel, without programming? Isn't there a way of reading the worksheet name contained in a formula?

20040715, 11:39 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How can I 'read' a formula? (2000)
Well, it can be done, sort of.
If your cell to "evaluate" is cell A2 and you are in the cell to the immediate right (B2), define this name:
Name: GetSheetRef
Refers To: =MID(GET.CELL(6,A2),2,FIND("!",GET.CELL(6,A2))2)
Now in cell B2 enter
=GetSheetRef
Do NOT copy cell B2 to another worksheet, or Excel will CRASH.
You can now enter =GetSheetRef in any cell and it will extract the sheetname of the formula (of that simple format) of the cell to the immediate left.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20040715, 11:40 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: How can I 'read' a formula? (2000)
There are addins, such as MoreFunc, that let you retrieve the formula of a cell without programming yourself (the programming has been done by the developer of the addin)
What about formulas such as =Sheet1!A1+Sheet2!A1 or =Sum(Sheet1:Sheet5!A1) or =INDIRECT("Sheet"&A1&"!A3"). In the latter case, if A1 contains 9, the formula refers to Sheet9. It won't be easy to handle all these cases.