Home

update cellnames in formula

%3CLINGO-SUB%20id%3D%22lingo-sub-313021%22%20slang%3D%22en-US%22%3Eupdate%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313021%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20huge%20excel%20sheet%20with%20a%20lot%20of%20simpleformulas.%20now%20i%20want%20to%20give%20all%20cells%20a%20name%20so%20the%20formula%20should%20be%20easy%20to%20understand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20convert%20my%20formula%20for%20example%2C%20D4%3A%20%22%3DD2*D3%22%20to%20(when%20i%20named%20D2%3A%20%22name1%22%20and%20D3%3A%20%22name2%22)%20D4%3A%20%22%3Dname1*name2%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20i%20have%20to%20do%20this%20manualyfor%20all%20formulas%20i%20need%20a%20few%20years.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advanced!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-313021%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313112%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313112%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20have%20to%20define%20names%20first%20(e.g.%20Name1%20for%20D2%2C%20etc)%20and%20after%20that%20apply%20defined%20names%20to%20the%20formulas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313074%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313074%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20and%20change%20some%20of%20the%20options.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313070%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313070%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20for%20the%20quick%20reply%2C%20this%20works%20not%20as%20I%20hoped.%20can%20not%20just%20select%20the%20cell%20and%20apply%20names.%20then%20i%20get%20an%20error.%20for%20no%20names%20found.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313039%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313039%22%20slang%3D%22en-US%22%3E%3CP%3EOr%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcelribbon.tips.net%2FT008266_Applying_Range_Names_to_Formulas%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelribbon.tips.net%2FT008266_Applying_Range_Names_to_Formulas%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313037%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313037%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Niels%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsourcedaddy.com%2Fms-excel%2Fapplying-names-to-existing-formula.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EUse%20%22Apply%20names...%22.%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-570420%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-570420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20get%20apply%20names%20to%20work.%20I%20get%20an%20error%20popup%20window%20that%20says%3A%20%22Microsoft%20Excel%20cannot%20find%20any%20references%20to%20replace.%20The%20formula%20that%20contains%20that%20cell%20reference%20that%20I%20want%20to%20replace%20consists%20of%20the%20following%3A%26nbsp%3B%3DB165*('Cover%20Page%20Major%20Assumptions'!%24B%2426)*-1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20replace%20the%20reference%20to%20B26%20with%20the%20named%20range%20Total_Income_Tax_Rate.%20B26%20is%20actually%20located%20on%20a%20separate%20sheet%20-%20and%20the%20scope%20of%20the%20name%20is%20%22workbook%22.%20I%20cannot%20get%20%22Apply%20Names%22%20to%20work!!%20It%20is%20very%20frustrating.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575813%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F137877%22%20target%3D%22_blank%22%3E%40Ron%20Haggin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20looks%20like%20that%20references%20with%20a%20sheet%20name%20will%20not%20change%20to%20named%20ranges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575851%22%20slang%3D%22en-US%22%3ERe%3A%20update%20cellnames%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575851%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20I%20appreciate%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20person%20suggested%20using%20Find%20and%20Replace%20which%20I%20think%20will%20work%20fine%20and%20won't%20be%20hard%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20funny%20-%20I%20use%20Find%20and%20Replace%20all%20the%20time%20and%20never%20for%20one%20second%20considered%20it%20for%20this%20problem.%20Sometimes%20the%20solutions%20are%20so%20simple%20-%20yet%20not%20obvious%20when%20you%20get%20fixated%20on%20doing%20it%20a%20certain%20way.%20My%20fixation%20on%20Apply%20Names%20prevented%20me%20from%20thinking%20it%20through%20clearly.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Niels07
New Contributor

I have a huge excel sheet with a lot of simpleformulas. now i want to give all cells a name so the formula should be easy to understand.

 

I want to convert my formula for example, D4: "=D2*D3" to (when i named D2: "name1" and D3: "name2") D4: "=name1*name2".

 

When i have to do this manualyfor all formulas i need a few years. 

Thanks in advanced!

8 Replies

Hello Niels

 

Use "Apply names...".

thanks for the quick reply, this works not as I hoped. can not just select the cell and apply names. then i get an error. for no names found.

Try and change some of the options.

 

You have to define names first (e.g. Name1 for D2, etc) and after that apply defined names to the formulas

@Detlef Lewin 

 

I cannot get apply names to work. I get an error popup window that says: "Microsoft Excel cannot find any references to replace. The formula that contains that cell reference that I want to replace consists of the following: =B165*('Cover Page Major Assumptions'!$B$26)*-1

 

I am trying to replace the reference to B26 with the named range Total_Income_Tax_Rate. B26 is actually located on a separate sheet - and the scope of the name is "workbook". I cannot get "Apply Names" to work!! It is very frustrating. 

@Ron Haggin 

It looks like that references with a sheet name will not change to named ranges.

 

@Detlef Lewin 

Thank you. I appreciate your help.

 

Another person suggested using Find and Replace which I think will work fine and won't be hard to do.

 

It's funny - I use Find and Replace all the time and never for one second considered it for this problem. Sometimes the solutions are so simple - yet not obvious when you get fixated on doing it a certain way. My fixation on Apply Names prevented me from thinking it through clearly. 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies