Dave:

Thanks for your reply. I have seen this response. The problem is that I
named the whole column "Profit", not just one cell. E.g. I started with a
reference to C13; then I used InsertNameApply to get "Profit"; then I used
Jim's solution, but it gave me "C1:C65536" instead of "C13". How do I get
back to C13 which is where I started?

"Dave Peterson" wrote:

Jim Rech posted a nice response at:
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

Aaron wrote:

I recently named the columns in a spreadsheet full of formulas. I then used
InsertNameApply to update my cell references with the names. E.g. if the
formula in A13 references C13 and I named column C €œProfit€�; A13 now
references €œProfit€� instead of C13. I want to reverse the process and change
the references back to the way they were. E.g. I want to change €œProfit€�
back to C13.

All formulas operate on the same row but in different columns. E.g. the
formula in B17 references other columns, but always row 17. Please help me
change my formulas back to cell references instead of names.

--

Dave Peterson