Changing cell references in formulas to names and back again.

[Guest]

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]

Jim Rech posted a nice response at:
http://groups.google.com/groups?threadm=u3ZAo#FmAHA.2048@tkmsftngp03

From: Jim Rech ([email protected])
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

 

[Guest]

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?

 

[Peo Sjoblom]

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

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

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 (e-mail address removed) if you want to contact me.