sheets back to formulas
 

I have a client who wishes to replace the sheet names in his cells with the
formulas they reference. I can see how you go from formula to sheet name, but
how about the other way around?

TIA......

I am not sure what you want.
If you want to see just the formulas goto <tools<options<view and click
on window options formulas
this will effect the enverything
for certain cells you can copy the cell and add a "'" infront of where you
paste it.

"J. Freed" wrote:

I have a client who wishes to replace the sheet names in his cells with the
formulas they reference. I can see how you go from formula to sheet name, but
how about the other way around?

TIA......

What my client wants is to replace a name with the formula it came from. For
example, if I have a defined name called "smith" and it references cell A1
and "smith" is being used in a cell somewhere, he wants to replace "smith"
with A1.

"bj" wrote:

I am not sure what you want.
If you want to see just the formulas goto <tools<options<view and click
on window options formulas
this will effect the enverything
for certain cells you can copy the cell and add a "'" infront of where you
paste it.

"J. Freed" wrote:

I have a client who wishes to replace the sheet names in his cells with the
formulas they reference. I can see how you go from formula to sheet name, but
how about the other way around?

TIA......

If you don't have many, use the Edit<Find Replace and replace smith With A1
this is awkward.

"J. Freed" wrote:

What my client wants is to replace a name with the formula it came from. For
example, if I have a defined name called "smith" and it references cell A1
and "smith" is being used in a cell somewhere, he wants to replace "smith"
with A1.

"bj" wrote:

I am not sure what you want.
If you want to see just the formulas goto <tools<options<view and click
on window options formulas
this will effect the enverything
for certain cells you can copy the cell and add a "'" infront of where you
paste it.

"J. Freed" wrote:

I have a client who wishes to replace the sheet names in his cells with the
formulas they reference. I can see how you go from formula to sheet name, but
how about the other way around?

TIA......

If the named range and the formula are on the same worksheet, then this post by
Jim Rech will help:

http://groups.google.com/groups?thre...%40tkmsftngp03

From: Jim Rech )
Subject: Can I "De-Name" Formula Cell References?
Newsgroups: microsoft.public.excel.misc, microsoft.public.excel
Date: 2001-02-16 13:32:51 PST

To do it to a cell or two first turn on Transition Formula Entry under
Tools, Options, Transition. Then go to the cell and press F2 and Enter.
When you turn off TFE the formula references should be de-named.

If you have a lot of cells to de-name select the range and run this macro:

Sub Dename()
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
For Each Cell In Selection.SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
ActiveSheet.TransitionFormEntry = False
End Sub

--
Jim Rech
Excel MVP

====
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

J. Freed wrote:

What my client wants is to replace a name with the formula it came from. For
example, if I have a defined name called "smith" and it references cell A1
and "smith" is being used in a cell somewhere, he wants to replace "smith"
with A1.

"bj" wrote:

I am not sure what you want.
If you want to see just the formulas goto <tools<options<view and click
on window options formulas
this will effect the enverything
for certain cells you can copy the cell and add a "'" infront of where you
paste it.

"J. Freed" wrote:

I have a client who wishes to replace the sheet names in his cells with the
formulas they reference. I can see how you go from formula to sheet name, but
how about the other way around?

TIA......

--

Dave Peterson