Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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...... |
#2
|
|||
|
|||
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...... |
#3
|
|||
|
|||
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...... |
#4
|
|||
|
|||
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...... |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) | |||
formulas for employee time sheets | Excel Worksheet Functions | |||
how do I tell excell 2003 to print sheets back to front? | Excel Discussion (Misc queries) | |||
OLAP write back via formulas (functions), not read only pivot tab | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |