Today is Part II in our 'Named Range' miniseries. In Episode #1581, Bill looks at a common issue with Excel; Deleting a Worksheet results in a finding #REF! listed in the Name Manager -- and I really don't want them there. Learn how to deal with these empty references by removing them from the Name Manager list.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast: #REF in name manager.
Hey we're kind of on a string here talking about names let's keep going alright so here's a really common problem.
I have three worksheets today.
I've report one and there's a whole bunch of names here I use create from selection so I have a name for every numeric cell, I have report to, that has a whole bunch of names, and I have the notes page that has no names at all.
Now I've decided that I really just want report 1 and I want notes I don't need report 2, so we're going to right-click and delete that sheet, there's no dependencies between these there were no formulas here that were pointing to the other, they were all just standalone I had them all together but check out what happened when we go to the name manager there are a whole bunch of reference values now those are all of the names that were on sheet 2 and when I deleted sheet 2 or report 2, they didn't delete the names they continue to remain there and remain as reference errors I guess that's good if I had any formulas on report one then we're pointing to those that prevents it from becoming a name error but it still causes it to become reference error so check this out this new filter, new, it was there in Excel 2007, I can filter this to all the names with errors.
All right that gives me all those and then I can click on the first one, SHIFT click on the last one, delete and only have to answer this question once instead of every single time a nice improvement there to the name manager that lets me find all those names that showed up as reference errors and solve them.
Okay I want to thank you for stopping by, I will see you next time for another netcast episode from MrExcel.
Learn Excel from MrExcel podcast: #REF in name manager.
Hey we're kind of on a string here talking about names let's keep going alright so here's a really common problem.
I have three worksheets today.
I've report one and there's a whole bunch of names here I use create from selection so I have a name for every numeric cell, I have report to, that has a whole bunch of names, and I have the notes page that has no names at all.
Now I've decided that I really just want report 1 and I want notes I don't need report 2, so we're going to right-click and delete that sheet, there's no dependencies between these there were no formulas here that were pointing to the other, they were all just standalone I had them all together but check out what happened when we go to the name manager there are a whole bunch of reference values now those are all of the names that were on sheet 2 and when I deleted sheet 2 or report 2, they didn't delete the names they continue to remain there and remain as reference errors I guess that's good if I had any formulas on report one then we're pointing to those that prevents it from becoming a name error but it still causes it to become reference error so check this out this new filter, new, it was there in Excel 2007, I can filter this to all the names with errors.
All right that gives me all those and then I can click on the first one, SHIFT click on the last one, delete and only have to answer this question once instead of every single time a nice improvement there to the name manager that lets me find all those names that showed up as reference errors and solve them.
Okay I want to thank you for stopping by, I will see you next time for another netcast episode from MrExcel.
