function to give me cell reference

[Josh Craig]

Is there a function that returns the formula in another cell in text?

For example, if cell A1 has the formula:

=Sheet2!B2

I want B1 to say in text: Sheet2!B2

 

[Ashish Mathur]

Hi,

While on cell A1, go to Data > Text to columns > Delimited and select Other.
in the other box, type = and click on next. in the destination cell box,
select B1. Click on Finish. please note that this is not a dynamic
solution I.e. if the formula changes in cell A1, then the result in cell C1
will not change.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[ryguy7272]

You can try Ctrl+~
Does that do it for you?

HTH,
Ryan--

 

[Josh Craig]

Hi Ashish. Thanks for the answer. So there's definitely no dynamic way to
do it?

Would you know if there's a way to get a column in a summary sheet to
display the names of all the other sheets?

e.g.

Col A (in Sheet4):
Sheet1
Sheet2
Sheet3

 

[Joerg Mochikun]

For a dynamic way, you could use a user defined function.

Go to the VBA Editor (Alt+F11), in the left Project pane click on your
workbook (VBA Project (workbookname)), rightclick and insert a module.
Doubleclick the new module (probably named Module1), and into the right text
field copy following function:

Function CellFormula(c)
CellFormula = c.Formula
End Function

Now you can go back to your workbook and enter into B1 the formula
=CellFormula(A1).

Cheers

Joerg Mochikun

 

[T. Valko]

So there's definitely no dynamic way to do it?

Try this UDF (user defined function)

Function GetFormula(cell_ref As Range) As String
GetFormula = ""
If cell_ref.HasFormula Then
GetFormula = cell_ref.Formula
End If
End Function

To install it:

Open the VBE - ATL F11
Open Project Explorer - CTRL R
Locate your file name. It'll look like this: VBAProject (your_file_name)
Right click on your file name.
Select Insert>Module
Copy/paste the code above into the window that opens on the right.
Close the VBE and return to Excel - ALT Q

To use it on your worksheet - use it like any other function:

=GetFormula(A1)

If the referenced cell does not contain a formula GetFormula returns a
blank.