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
Teams Updater Vulnerability
Andrew Matthews in Microsoft Teams on
15 Replies
HowTo OMA-URI - something not working
PatrickF11 in Microsoft Intune on
19 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies