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.

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

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

Insertnamedefine, select the name, then select C13, click add, then OK

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Aaron" wrote in message
...
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 ?oProfit?�; A13
now
references ?oProfit?� 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
?oProfit?�
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

Peo:

Thanks for your reply. The problem is that C13 is just an example. There
are thousands of references to thousands of rows and columns. So if there
was a formula in A2 referencing C2 than there was also a formula in A3
referencing C3 and so on and much more complicated. Now A2 and A3 simply
reference €œProfit€� instead of C2 and C3 respectively. This is because
€œProfit€� is now the name of column C. Now I dont know how Excel knows that
€œProfit€� in A2 is for C2 and €œProfit€� in A3 is for C3, but it does know
because the calculations are correct. But I want the cell refs back not the
cell range. Can I email someone a spreadsheet so you can see what I mean?
My email is if you want to contact me.


"Peo Sjoblom" wrote:

Insertnamedefine, select the name, then select C13, click add, then OK

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Aaron" wrote in message
...
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 â?oProfitâ?�; A13
now
references â?oProfitâ?� 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
â?oProfitâ?�
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