formual to get reference contents

[Guest]

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

 

[Guest]

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

 

[Guest]

Michael,

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

Thanks anyway.

 

[Guest]

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

 

[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

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