Excel - How To Get Sheet Name In A Cell In Excel - Episode 1982

Опубликовано: 09 Август 2016
на канале: MrExcel.com

10,424

Microsoft Excel Tutorial - formula to put the worksheet name in a cell.

Welcome to another episode of the MrExcel podcast! In today's episode, we will be discussing how to get the worksheet name into a cell in Excel. This is a handy trick that can save you time and effort when working with multiple worksheets in a workbook. So let's dive in and learn how to do it!

First, I want to thank our sponsor, "MrExcel XL", a book filled with over 40 Excel tips, cartoons, cocktails, tweets, and jokes. Make sure to check it out for some fun and useful Excel knowledge.

In yesterday's episode, I showed you how to use the fast worksheet copy by CTRL-dragging. Did you notice that the worksheet title automatically changed? Well, today I will show you how to do that using a formula. It all starts with the formula =CELL("filename"). This formula returns the complete path, workbook name, and sheet name.

However, this gives us more information than we need. So we need to find the right square bracket using the FIND function. This tells us the position of the bracket, and we know that the sheet name is one character after that. So we use the MID function to extract the sheet name from the cell. You can adjust the length of the sheet name depending on your needs.

To put it all back together, we use the MID function again, this time starting at the position of the bracket and adding one character. Then we concatenate the word "Report" to the end. And just like that, we have the worksheet name in a cell. But I have to admit, this is not something I can remember easily, so I have to refer back to page 13 in the book every time I need to use it.

But there is hope! In July, someone named Anonymous created an idea on Excel.UserVoice.com for a SHEETNAME() and SHEETNAMES() function. This would make our lives so much easier, and I highly recommend voting for this idea. You can use the short link mrx.cl/sheetname to cast your vote.

In the meantime, I will be podcasting this entire book throughout the month, so make sure to subscribe to the MrExcel Excel playlist to catch all the episodes. Or, if you want to save time, you can purchase the book right now and use it as a handy reference for all the tips and tricks we discuss this month.

To recap, we learned how to use the CELL("filename") function to get the worksheet name into a cell. Then we used the FIND and MID functions to extract the sheet name and put it back together with the CONCATENATE function. But let's all vote to have the Excel team fix this and make our lives easier!

Thank you for tuning in to today's episode. We hope you found this tip useful and we'll see you next time for another netcast from MrExcel! Don't forget to like, comment, and subscribe for more Excel tips and tricks. And make sure to check out our sponsor, "MrExcel XL", for some fun and informative Excel content. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

Table of Contents:
(00:00) Formula for Worksheet Name in a Cell
(00:21) Using fast worksheet copy and changing worksheet title automatically
(00:31) Formula for getting worksheet name into a cell
(01:17) Alternative solution for getting sheet name using SHEETNAME() and SHEETNAMES()
(02:17) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelhacks #excelformula #excelformulasandfunctions

This video answers these common search terms:
how to get a sheetname using a formula excel
how to get sheet name in a cell in excel
how to put sheet name in excel cell
how to show sheet name in excel
how to insert sheet name in excel
how to get worksheet name in excel
how do i display the sheet name ion excel
how to add file path to excel sheet
how to insert file path in excel cell

Use a formula in Excel to put the worksheet name in a cell. Also in this episode: =CELL("filename",A1) returns the path and file name
Look for the right square bracket using FIND
Add 1 character to the result of FIND
Pass that to MID
Uservoice votes at http://mrx.cl/sheetname

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

Смотрите видео Excel - How To Get Sheet Name In A Cell In Excel - Episode 1982 онлайн, длительностью часов минут секунд в хорошем качестве, которое загружено на канал MrExcel.com 09 Август 2016. Делитесь ссылкой на видео в социальных сетях, чтобы ваши подписчики и друзья так же посмотрели это видео. Данный видеоклип посмотрели 10,424 раз и оно понравилось 64 посетителям.

48,459

952