formual to get reference contents

J

John M

Hi,
How can i get the reference contents from a cell? This may be explained
with an example. Cell contains "Sheet1!A5" which equates to 20 say. If i
use cell function i can only get 20. How can i extract the "Sheet1!A5"
information.

Thanks in advance
 
M

Michael

One way is to select Tools -- Options, and on the View tab, check Formulas.
Your spreadsheet will now show formulas, not results. Print it and then
deselect formulas on the View Tab. HTH
Sincerely, Michael Colvin
 
J

John M

Michael,

I want to get it from a formula and not just to view them.

Thanks anyway.
 
P

pinmaster

Hi John

I've seen a similar request not too long ago. Someone had an answer using a
UDF. A search might come up with something.

Regards!
Jean-Guy
 
R

Ron Rosenfeld

Hi,
How can i get the reference contents from a cell? This may be explained
with an example. Cell contains "Sheet1!A5" which equates to 20 say. If i
use cell function i can only get 20. How can i extract the "Sheet1!A5"
information.

Thanks in advance
Click to expand...

You will need a UDF in VB.

<alt-F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, enter the formula

=ShowFormula(cell_ref) into some cell. It will display the actual contents of
cell_ref

=============================================
Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function
=============================================


--ron
 
Top