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
Highlighted
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
Highlighted

Hello Niels

 

Use "Apply names...".

Highlighted
Highlighted

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.

Highlighted

Try and change some of the options.

 

Highlighted

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

Highlighted

@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. 

Highlighted

@Ron Haggin 

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

 

Highlighted

@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.