Changing cell references in formulas to names and back again.

I recently named the columns in a spreadsheet full of formulas. I then used
Insert>Name>Apply 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 (jarech@kpmg.com)
Subject: Re: 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
> Insert>Name>Apply 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 Insert>Name>Apply 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 (jarech@kpmg.com)
> Subject: Re: 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
> > Insert>Name>Apply 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
>

Insert>name>define, select the name, then select C13, click add, then OK

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Aaron" <Aaron@discussions.microsoft.com> wrote in message
news:48F99797-3592-4F24-B673-74C67DC5E5F8@microsoft.com...
> 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 Insert>Name>Apply 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 (jarech@kpmg.com)
>> Subject: Re: 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
>> > Insert>Name>Apply 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 don’t 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 aaron.prato@us.atlascopco.com if you want to contact me.


"Peo Sjoblom" wrote:

> Insert>name>define, select the name, then select C13, click add, then OK
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "Aaron" <Aaron@discussions.microsoft.com> wrote in message
> news:48F99797-3592-4F24-B673-74C67DC5E5F8@microsoft.com...
> > 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 Insert>Name>Apply 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 (jarech@kpmg.com)
> >> Subject: Re: 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
> >> > Insert>Name>Apply 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
> >>
>
>
>