Return Sheet Name into a Cell - Excel Formula
HTML-код
- Опубликовано: 29 сен 2024
- Return the name of a sheet into a cell using an Excel formula. This video tutorial explores the use of the CELL, MID and FIND functions to display the worksheet name in a cell.
By doing this with a formula, if the sheet name changes in the future it will automatically update in the cell. This may be useful to update other formulas such as hyperlinks.
Master more than 100 Excel Formulas - goo.gl/QBaF1m
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
=RIGHT(CELL("filename",B1),LEN(CELL("filename",B1))-FIND("]",CELL("filename",B1),1))
THANKS.
For those want to copy paste:
; version
=MID(CELL("filename";B1);FIND("]";CELL("filename";B1))+1;32)
, version
=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,32)
=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,32)
Any reason why this function would break on the mac version when using a copy version of a sheet? Every time I make a copy of a new sheet the existing functions will update to the last entered function. Thanks!
Hi Alan.. great tip and video. I've used this formula for years to tag each sheet in a workbook with its name in a cell and then pass the cell values to VBA code for running a print routine on the sheets. That way, if I change my sheet name on the fly, I don't have to edit the code to keep the print macro working. I have code that passes the new name to the Sheets().PrintOut command via the Range().Value command. Through some Internet research, I learned that the file format for EXCEL is capable of a sheet name of 255 characters, but in actual use, Microsoft limits it to 31 characters. So, I've seen some people use 31 for the number of characters to display in the MID function and others use 255, presumably to have their formulas ready if MS ever increases the usable limit above 31. Anyway, it is a great tip and very useful. Thanks again and Thumbs up!
Thanks for the great video! helped a lot. I wish MS Excel's product manager could see this. Really don't they think there should be a dedicated formula for this?
You're welcome Erez. Maybe one day. Some amazing developments in Excel recently and in the near future though.
can you pleaspload the specific workbook for this video, or can you put the formula text into the comments section so we can easily copy pasete. thanks
You Rock! Thanks for explaining each text purpose.
My pleasure. Thank you.
A fantastic video - well explained and exactly what i needed for my excel sheet. I loved that you explained how excel is "seeing" a function like this - this help alos in general for formulas for the future. Thanks
You're welcome Bianka. Thank you.
I've been using this formula for a long time but never understood how or why it worked. But now I do. Thanks for the great explanation!
You're welcome. Thanks!
I tried using my newly opened excel file with no name yet then it turned out without a value. i saved the file in a location with any name then i works.
Shall we extract sheet name in IF formula of Excel ?
Thank you so much for this, this is a tremendous help.
That is great to hear. You're welcome, Louise.
Exactly the info I was looking for; many thanks!
yes i did it
thank you so much
It doesn't work for me, why is that?
I can't say without seeing it.
Great video, thanks for sharing your wizardry with this function. Accomplished exactly what I was needing to do and will incorporate it into other workbook as well! Keep up the good work!
Thank you very much, Rowland.
I am eager to get this working but it wont run, keeps saying too many arguments and when I press on ok it homes in on the +1... please help.
Too many arguments missing that you have too many commas, or missing a closing bracket.
How do I find the sheet name code in Excel?
Wow,thanks that's actually I am searching on RUclips
Welcome, Ankit 😊
I follow exactly like the video but why i get #Value! ? My version is 2010.
The version should not be an issue here. The #VALUE error normally indicates an issue with formatted. My first guess would be missing "" around filename. I would double check the formula for what might be missing.
When i try at home, i type =Cell("filename, A1") enter, the cell is blank, no full path showing. But when i try at my company its works. Weird...
The formula should read =CELL("filename",A1)
Sorry, i miss type in the comment, actually i type same as your, not working at my home pc, but it works at my company pc.
My next guess would be to check that you have saved the file, otherwise I am not sure.
Thank you for posting this video. Is there a slick method for doing this the other way around?
It would have to be with VBA
Thanks for the useful information.
You're welcome Steve.
I want to name the tab based on a cell in my worksheet not the other way around. I have a spreadsheet to track my time and I want the tab named for the cell which contains the last day of the pay period. I've looked/tried several macros I found on the internet and I can't make any of them work. Most likely operator error as what I know about Excel would fit in a thimble. I'm on Win7, Excel 2013.
Should not be too difficult. If this cell contains a date though then it may be symbols that are not allowed in sheet names causing the problem.
The code below will assign the value of a cell to a sheet name. n the example below it is the value in cell F3 to the first worksheet of a workbook.
Worksheets(1).Name = Range("F3").Value
If you are not sure what cell the last day of the pay period would be ahead of time then the macro would need to find it first. You also need to know when you want the macro to run.
Thanks. I was able to make this code work.
Private Sub Worksheet_Calculate()
If Range("f1").Text Me.Name Then Me.Name = Range("f1").Text
End Sub
Awesome! Worksheet Calculate event. Good work.
How to get sheet name in Google Sheets?
From another sheet on same file from sheet URL adres?
I don't use Google sheets much and am unaware of the approach there.
@@Computergaga I've already found:
=if(today()=today();regexreplace(cell("address";'SHEETNAME'!$A$1);"'?([^']+)'?!.*";"$1");"")
Excellent!
Hi, Can you please explain how to do this in Google spreadsheet ?
Sorry, I do not use Google Sheets.
@@Computergaga ok thank you
Awesome tutorial!!!!! Thanks!!!!
You're welcome, thank you.
great little trick Alan, many thanks
Your welcome, Mohideen.
Love your excel tips!
Is there a way to have the name of the last sheet in a work book display in a cell inside the first sheet? In other words, if a new sheet is added at the end, it will automatically update the newest sheet name ?
I think this would only be possible with Excel VBA and creating a custom function.
Computergaga, thanks for the reply
Do I have to save the file, before doing it?
Yes Shubhasish.
Any similar formula that can be used in Google spreadsheet?
I'm not too familiar with Google sheets, but I know a lot of the formulas are similar.
any luck
brilliant
Thank you AngelesFam.
magic
Thank you.
Thank you so much! Great hack!
Thank you, Akshay 👍
Nice tip. Thank You.
Thank you Joshua.
very good!
Thank you Luigi.
Can we get author name like this??
Sure Mithun. You can extract text between the two / such as in this video - ruclips.net/video/Yf-aqzuuQzE/видео.html
Using the CELL fucntion for the filename.
Computergaga thank you: rather then extracting from text. Can’t we get through direct function??
Yes sure. you don't need to reference a cell with text if that is what you mean. Can get it direct through the CELL function and other formulas to extract.
There is no formula to directly get the author name, but you can with VBA.
Fine work
Thank you Thijs.
Excellent
Thank you Majid.
I wonder why people dislike this wonderful video.....
😜