This is Part III of III in MrExcel's 'Named Range' Series this week. Today, in Episode #1583, Bill looks at Worksheet Level Names. Worksheet Level Names are good per Worksheet, but not throughout the Workbook you are using. Follow Bill in today's Podcast to see how to employ Worksheet Level Names to make your annuals -- and a lot more -- easier to work with in Excel!
...This 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
...This 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
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast Worksheet Level Names.
Hey welcome back to the MrExcel netcast so we’re kind of on a streak here talking about name ranges and one of the things that I discovered is that you can run into a lot less problems with names if you use worksheet level names instead of workbook level names.
So if I would just create a name right here it's going to become a workbook level name and is available all the time but with worksheet level names it's only available on this worksheet.
Ok so here I'm on the January report but I know I’m going to have sheets for Jan, Feb, Mar, April, May, I'm going to have 12 sheets by the end of the year and I want them all to have the same names now rather than have to go into formulas define name and choose worksheet as the scope, check this out, you can actually come here and type JAN!REV if we want to call that revenue and then here type JAN!COGS for cost of goods sold and then here JANGP, all right so what I've done is I've created some worksheet level names, so here we'll put in 1000 here, 800 there and then the formula revenue minus cost of goods sold right so the formulas are working just come here and say =REV, you know it all works but if I go to another sheet and type =RE, there is no name range that it knows up there.
Now if I needed to, I could still say =JAN!REV and it would work but I'm now free to come here and call this FEB!REV and FEB!COGS and FEB!GP, now of course if you have spaces in the worksheet name then you have to put apostrophes around the worksheet name before the apostrophe here but it'll work out and you always end up with worksheet level names instead of workbook level names.
Usually what happens is we create the names here on January and then we control drag to make a copy for March and then we have one set of workbook level names and once in a worksheet level names and it's just this big mish-mosh.
Let's just take control the thing, create worksheet level names from the get-go especially we're going to have all of these worksheets with identical names.
That was a great tip thanks to Dan one of the PM's on the Excel team for that heads up with that tip.
Well hey I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast Worksheet Level Names.
Hey welcome back to the MrExcel netcast so we’re kind of on a streak here talking about name ranges and one of the things that I discovered is that you can run into a lot less problems with names if you use worksheet level names instead of workbook level names.
So if I would just create a name right here it's going to become a workbook level name and is available all the time but with worksheet level names it's only available on this worksheet.
Ok so here I'm on the January report but I know I’m going to have sheets for Jan, Feb, Mar, April, May, I'm going to have 12 sheets by the end of the year and I want them all to have the same names now rather than have to go into formulas define name and choose worksheet as the scope, check this out, you can actually come here and type JAN!REV if we want to call that revenue and then here type JAN!COGS for cost of goods sold and then here JANGP, all right so what I've done is I've created some worksheet level names, so here we'll put in 1000 here, 800 there and then the formula revenue minus cost of goods sold right so the formulas are working just come here and say =REV, you know it all works but if I go to another sheet and type =RE, there is no name range that it knows up there.
Now if I needed to, I could still say =JAN!REV and it would work but I'm now free to come here and call this FEB!REV and FEB!COGS and FEB!GP, now of course if you have spaces in the worksheet name then you have to put apostrophes around the worksheet name before the apostrophe here but it'll work out and you always end up with worksheet level names instead of workbook level names.
Usually what happens is we create the names here on January and then we control drag to make a copy for March and then we have one set of workbook level names and once in a worksheet level names and it's just this big mish-mosh.
Let's just take control the thing, create worksheet level names from the get-go especially we're going to have all of these worksheets with identical names.
That was a great tip thanks to Dan one of the PM's on the Excel team for that heads up with that tip.
Well hey I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
