de-apply names

I want to replace the names in formulas with their cell references. 
Basically, I want to undo the applying of a name. How can this be 
accomplished?

The reason I need to undo names is that I applied some names that were 
incorrectly defined as relative columns instead of absolute columns. This 
caused a lot of unintended cell references to change to the applied name. 
Although the spreadsheet seems to calculate correctly the applied name is 
not appropriate. Therefore I need to change these names back to cell 
references.

Thanks for any help
Don 


0
Don
4/19/2010 3:29:57 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1335 Views

Similar Articles

[PageSpeed] 52

Have you tried using Find & Replace (CTRL-H) ? Select the offending
cells first.

Hope this helps.

Pete

On Apr 19, 4:29=A0pm, "Don" <don87...@hotmail.com> wrote:
> I want to replace the names in formulas with their cell references.
> Basically, I want to undo the applying of a name. How can this be
> accomplished?
>
> The reason I need to undo names is that I applied some names that were
> incorrectly defined as relative columns instead of absolute columns. This
> caused a lot of unintended cell references to change to the applied name.
> Although the spreadsheet seems to calculate correctly the applied name is
> not appropriate. Therefore I need to change these names back to cell
> references.
>
> Thanks for any help
> Don

0
Pete_UK
4/19/2010 3:45:43 PM
I'd do this against a copy of the file...

Jim Rech posted a nice response at:
http://groups.google.com/groups?threadm=u3ZAo%23FmAHA.2048%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


Don wrote:
> 
> I want to replace the names in formulas with their cell references.
> Basically, I want to undo the applying of a name. How can this be
> accomplished?
> 
> The reason I need to undo names is that I applied some names that were
> incorrectly defined as relative columns instead of absolute columns. This
> caused a lot of unintended cell references to change to the applied name.
> Although the spreadsheet seems to calculate correctly the applied name is
> not appropriate. Therefore I need to change these names back to cell
> references.
> 
> Thanks for any help
> Don

-- 

Dave Peterson
0
Dave
4/19/2010 4:48:57 PM
Reply:

Similar Artilces:

File Message on a Name or subject
I am looking for a way to file my messages by the Name of sender or subject...I would like the solution to be able to create the folder for this as well, so I do not have to create the folders before then set rules up... can this be achieved? Regards You would need a third party utility, if such exists, but then any spam would also be filed "PR" <paul.raeburn@vodafone.net> wrote in message news:uZkwjQofJHA.1744@TK2MSFTNGP03.phx.gbl... >I am looking for a way to file my messages by the Name of sender or >subject...I would like the solution to be able to create ...

Shared Calendars not Displaying User Name
Outlook 2003 / Exchange 2003, both fully patched and running on XP Pro SP2. Shared calendars added under "Open a shared calendar". Header of calendars only displays "Calendar", not the username of the shared calendar owner. How can I get Outlook to display the username, please. At the moment I have multiple calendars open and they all are headed "Calendar". Not very useful :-( TIA, Bill http://www.outlook-tips.net/archives/2005/20050809.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for W...

V9 Home page name
Have an employee that got married and changed last names, I have changed the name in payroll cards but on her home page (top left) it still shows the old last name. How do I change the name that appears on the home page? thanks! -- Doug Doug the user's name at the top of the home page comes from Tools ->Setup -> System -> User. -- Usha "Doug" wrote: > Have an employee that got married and changed last names, I have changed the > name in payroll cards but on her home page (top left) it still shows the old > last name. > > How do I change the...

named range refers to: in a chart
i have a simple line chart. i have a named range "Current" the range refers to: OFFSET(INDIRECT(CELL("address",OFFSET(A1,MATCH(LARGE(A:A,1),A:A,0),2,1,1))), 0,0,1,COUNTA(INDIRECT((ROW(OFFSET(A1,MATCH(LARGE(A:A,1),A:A,0),2,1,1)))&":"& (ROW(OFFSET(A1,MATCH(LARGE(A:A,1),A:A,0),2,1,1)))))-1) however, when i try to use this named range as the data range for my chart, it gives me an error "Reference not valid" the formula above returns a range of 1 row by about 25 columns. so its only 2 dimensional. why can't the chart use this named range? TIA...

Outlook Mail Item Duplicate "Display Name" Problem
Dear all, I'm currently writing a Persona Menu smart tag in Outlook 2003. I use dynamic caption and intend to modify the "Additional Actions" sub-menu depending on the contact(in the MAPIFolder) that the tag is referring to. Problem occurs in the mapping of the tag and OL contact. All information I can find from the smart tag action DLL(with ISmartTagAction2 interface) is just the tagged text string from get_VerbCaptionFromID2() but not the entity object that calls the persona menu. So my way to complete the chain is to : 1) search the sender and recipients' "display...

Named Ranges #3
Is there a limit on the number of non-adjacent ranges you can give a Named range to? If I wanted to select cells A1:d1;a5:d5;a17:d17 and continue this for the next 10 selections, will I run out of how many selections I can make? Is there a limit to the amount of selections for named ranges? Thanks Yes, there is a limit of approximately 255 characters. May sound like a lot, but XL adds the sheet name to each individual cell address in the range. So, a WS named "Num1" will be able to accommodate a larger named range then a WS named "EasternSalesProjections". If you d...

how can i apply a footer in every .exl file
plz give me solution for this question Open a new workbook and set your footers/headers and name the book as book.xlt and save it in your XLStart folder. When you click on the New Icon on the standard toolbar a new workbook will be opened based on this settings -- Jacob (MVP - Excel) "rakesh" wrote: > plz give me solution for this question You could use a template workbook (like Jacob suggests) for new workbooks. But that won't help you for existing sheets in existing workbooks. And the book.xlt workbook won't help for adding sheets to any work...

Informacion a Cerca de Vender MS CRM 3.0 En LIma
Saludos... Me gustaria saber.. con quien podria comunicarme para poder ser un distribuidor! del producto Crm 3.0 en Lima Peru ...

Exchange directory name of mailbox has heap of numbers, eg. jsmith2356634346
I have added some text from another post as people will probably search on something out of the exmerge log to find this post. Does anyone know why Exchange 2003 will automatically adds extra numbers to the end of a directory for a mailbox, when you look at the AD user profile, it all looks fine, mailbox name looks fine. The only thing that i can see that it effects is exmerge when it is looking for a PST with a heap of numbers at the end. Does any one have the answer? The simple solution is to rename the pst to what the directory name is, but that dont answer my question of why the number...

Names Translation
Hello I made program on MS Access 2007. I am thinking to make possibility for translate(changes) the "menu, buttoms and columns name". For the columns change so such as: Original columns name Changes to other Name Namn Price1 Pris1 Price2 Pris2 After converting to MDE. I mean when I give my work as MDE to anyone, so he can change the word that needs for forms and raports. Any comments appreciate. Thanks On Wed, 21 Apr 2010 07:14:01 -0700, Highlight between 3...

How to insert Company name at the end of each page?
Does anyone have any suggestions on how to insert Company name at the end of each page? Thanks in advance for any suggestions Eric On Sun, 14 Mar 2010 00:43:01 -0800, Eric <Eric@discussions.microsoft.com> wrote: >Does anyone have any suggestions on how to insert Company name at the end of >each page? >Thanks in advance for any suggestions >Eric Type it into the footer. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. ...

How to put a table in Slide Layout so it can be applied to a page?
I am working with Slide Masters and Layouts for the first time. I'm doing OK with making them and applying them to pages. But when I make a table it doesn't work. When I go to Insert>Layout Placeholder> Table, then I get a box that has the word "Table" in it. Is that right? When I look for controls to make it look like a table with headers, cells etc., right-click on it, whatever, I don't see any table controls, just the usual text/box attributes (size etc.). So in the ribbon I go to Table Styles and click on one of them (Light #5) and then I have a no...

How do I remove the sheet name from a named formula?
I would like to use the same name to refer to the same set of cells on different worksheets: SheetTitle=$A$1 but when I try this it reverts to SheetTitle=Sheet1!$A$1 Any ideas how to do it? I could use =IF(,,,) if I knew how to look up the currently active sheet; =IF(SheetName="Sheet1",TRUE,FALSE) Any ideas how to reference the name of the sheet? Well, you could do something like this: SheetTitle=!$A$1 The exclamation point means that the Name will refer to A1 on the active worksheet. Excel uses the SheetName to tell things apart. If I try to give to different A1'...

Installation exchange 2000 with a domain name
Dear All, I bought a domain name (glcinfo.com), and a PC for a server with Windows 2003 Enterprise. I would like install Exchange 2000 and create an emails address with my domain name. (ex : glecomte@glcinfo.com) I don't know how do ? Please can you help me ? Thanks in advance for your help. Best Regards Guillaume. Hi, That is not a simple question to answer. It all depends of your current setup. I would recommend starting here: http://www.microsoft.com/technet/prodtechnol/exchange/2003/library/messsyst.mspx Leif "Guillaume LECOMTE" <lecomteguillaume@wanadoo.f...

Account Names not showing when adding new user
Hi, I created a SharePoint site off of a 2007 Project Server install and when adding users to access the SharePoint site, directory services only returns admin users of the SharePoint/Project Server? How do I get AD to return all the users in the domain? Thanks! Steve Steve: What do you mean when you say "I created a SharePoint site off of a 2007 Project Server install?" Your description lacks a few details. "Steve" <Steve@discussions.microsoft.com> wrote in message news:EAA4C48D-9D6A-44C7-84BF-787AE762561E@microsoft.com... > Hi, >...

Obtaining selected printer name
Hi, Having called AfxGetApp()->SelectPrinter(m_hDevNames, m_hDevMode, FALSE); how do I obtain the printer name, e.g. "\\Hostname\Printername"? Also how do I get a handle to the selected printer? TIA Jan Found it: DEVMODE FAR * pDevMode = (DEVMODE FAR *)::GlobalLock(m_hDevMode); pDevMode->dmDeviceName; ::GlobalUnlock(m_hDevMode); Sorry for the nuisance. Its been a long day. Jan "Jan M" <jan@acu.no-ip.com> wrote in message news:#7MdFAhyHHA.5964@TK2MSFTNGP04.phx.gbl... > Hi, > > Having called > > AfxGetApp()->SelectPrinter(m_hDevN...

Place actual selection name in table instead of ID #?
I have a CBO that places the companies ID in my table instead of the actual companys name. I know this is how it is suppose to work but I need it to place the actual name that the user selects into the table. How is this done I know of a way? In that case you have to change the field in the designated table because initally you would store a number and now you want to store text. So change the field to text and then change the bound column of the CBO to 2 (which is probably the field which shows the name). This way you can still use the combo but it will store the name in the table (as...

mailbox name not allowed or chunk too large
Hello - I have a client who can't email us. Everytime he does, it bounces back to him and he gets this error message: <IHA4.IHA-CENTRAL.local #5.5.0 smtp;553 Requested action not >> taken: mailbox name not allowed or chunk too large - on relay of: MAIL >> FROM:<Mike_O'Donnell@theirdomain.com>> I found this link: http://www.faqs.org/qa/rfcc-193.html FW: DELIVERY FAILURE: 553 Requested action not taken: mailbox name not allowed or chunk too large. Apostrophies (') not supported in smtp address (reply to) by recepient server. So, looking at the erro...

Copying a named range with a changing cell reference
I am writing a macro to copy a sheet from a workbook to a new workbook, it has hiddden rows which i am not copying just values, however, I also want to copy the named ranges to the new sheet, the problem being the cell references change as I get rid of hidden rows and columns. Is there any way to carry over a named range, having it refering to values or cell content rather than actuall cell references................ Hi If you only need the values to be copied, you can use "Paste Special" and select values to be pasted. Regards, Per <jwilkes@sedgman.com.au> skrev i en med...

supply chart with names on price or quantity
I have a supply chart. I want to have the State name next to either price or quantity on the graph. How to do it? Perhaps one of these labeling utilities: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "chusu" <chusubaxter@hotmail.com> wrote in message news:9901b41a-2d58-4847-af7d-c564406c68cb@i12g2000prf.googlegroups.com... >I have a supply chart. I want to hav...

discounts not applying on transactions.
I cannot seem to get my item discounts to automatically apply during a transaction. I've tried the mix and match and the buy X, get Y options, and selected the items the discount should be applied to, but during a transaction, the items come up at the regular price. I'm pretty inexperienced with this program and I am getting frustrated. HELP please. When adding the items to the transaction are they meeting the requirements of your discount schemes? Can you give some examples? Rob "khopek" <khopek@discussions.microsoft.com> wrote in message news:72C9A557-6743-...

Apply a Function to a Button in a Form
I am trying to apply a function (wchich is contained in a module) to a button in a user input form that will read the value in once field of the form and convert that value to a different format in a different field in the form. Here is the function that I am trying to apply: Sub DegToDMS(ByVal L As Double, D As Integer, M As Integer, S As Double) ' ' Converts a decimal degree to Degrees, Minutes, and Seconds ' Seconds may contain up to 3 decimal places. ' e.g. 15.5 -> 15,30,0 ' D = Int(L) L = (L - D) * 60 M = Int(L) S = Val(Format((L - M) * 60, "#.###&q...

how to set up same name range in different sheets for different d.
please repost with a brief subject line and the question in the message body as it has been truncated. Cheers JulieD "kailash" <kailash@discussions.microsoft.com> wrote in message news:7740EA1A-9D11-4C3D-8499-3C7CDBC92D65@microsoft.com... > Hi see: http://www.xldynamic.com/source/xld.Names.html -- Regards Frank Kabel Frankfurt, Germany "kailash" <kailash@discussions.microsoft.com> schrieb im Newsbeitrag news:7740EA1A-9D11-4C3D-8499-3C7CDBC92D65@microsoft.com... > ...

present attributes instead of a name
Hello , Here is the code of the function I want to use: private TreeNode CreateTreeNodeFromXmlNode(XmlNode node) { TreeNode tmptreenode = new TreeNode(); if((node.HasChildNodes) && (node.FirstChild.Value != null)) { tmptreenode = new TreeNode(node.Name); TreeNode tmptreenode2 = new TreeNode(node.FirstChild.Value); tmptreenode.Nodes.Add(tmptreenode2); } else if(node.NodeType != XmlNodeType.CDATA) { tmptreenode = new TreeNode(node.Name); } return tmptreenode; } The problem is that insted of node.Name I want node.Attributes["name&...

Recipient policy
Ok, we began with (SEE Exchange 2k3 in Child domain - Best Practices) a parent domain AM.com and childe VA.AM.com. I need to build a Recipient Policy that makes different default Email addresses. the Parent domain needs %First.Last%@AM.com and the Child domain's users need to have %FirstInitial.LastName%@VDA-SYSTEMS.COM but don't necessarily need both. what do you think?? DJK Can create RP for child domain based on upn suffix (if you don't change the default for child domain accounts, it should be @childdomain.parentdomain.com). Or you could add a new UPN suffix for chi...