If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course
First of all thanks a lot, I've benefitted a lot from your videos, it has eased my work. As you've covered a lot in excel. Can you also teach us DAX measures
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) This formula will actually get the sheet name and not the ACTIVE sheet name as in the video (causing all sheets to show the same sheet name)
Well what the maker of this video forgot to mention is that in the CELL formula you should simply add a reference cell of the current sheet. So "A1" is a good reference. This means that his formula should also work but edited. It would look like this: =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename")))
Clever! Please answer some of the questions posed. My exact problem is for totalling data from monthly sheets into a summary sheet. I want to be able to copy/paste (say) January's data into the other 11 months' totals rows, referencing each month. Sumit's solution still does not let me do this.
Hi! Thank you! Your video is awesome! I had try it and it works on my computer. However, after I put it on office 365 the formula break. Can you please advice is it possible to do it for 365?
Thank you for such useful video. One of my summary file is connected to multiple source files kept in shared drive. Vales in these source files keep on changing daily basis. So I used '=' in my summary file to bring the values from source files. The problem is that every time I have to edit each link to derive the new values even after changing the settings under option. Update option under Data > edit links doesn't work. Sometimes it shows warning. Would be great to have your suggestions. Thanks
Hi Sumit Bansal Sir, Thanks for the above useful video tutorial. I am using the above formula described in the video, I get same sheet name in all sheets of the workbook. Every time I have to refresh the cell to get the correct sheet name. Any suggestions to resolve this problem.
Thank you for the clear tutorial. I've learned a lot from your videos! You have great timing as I am trying to do this very thing in a current workbook. I'm having trouble though. Works perfectly on Sheet1, but when I paste into Sheet2, it changes the name on both Sheet2 and Sheet1 to "Sheet2". Subsequently, when I save in Sheet3, it changes the names on all three worksheets to "Sheet3", and again with Sheet4, now they all have "Sheet4" as the name in the cell. It looks like the same thing happened in your example, when you changed from tab "Q4 2022" back to tab "Q1 2021" the name showing on Q1 tab is "Q4 2022". Is it supposed to do that? Is there a way to prevent changing the names on the previous sheets? Thank you!
Unfortunately it does that. The formula is dynamic and picks up the sheet name that is currently active, A workaround would be to hit the F9 key and it will force the formula to recalculate and give you the correct sheet name. Another, not so easy, solution would be to do it using VBA, where as soon as you change the sheet, it will force a recalculation
I had this problem too. One way I found to fix it was by adding a [reference] in the CELL() function: =TEXTAFTER(CELL("filename",A1),"]") where A1 can refer to any cell on that particular sheet. Hope this helps!
Sumit, question. I have an excel workbook with a summary sheet and other worksheet names as dates of a month (06 or 17 or 22 and so on). In the summary sheet, I want to pull data from sheets of a specific date. For ex in the summary sheet, if I use the date 06 on cell A2, then summary sheet B2 should show a value from sheet 06 a4. How can I achieve this? Thank you for helping me out with this.
Dear Sumit, thanks for your excellent videos...I have learnt a lot from them. I am having a small issue when I use your formula for getting the sheet names. The formula works very nicely, but if I copy it to other sheets in the same workbook, all worksheets change to display the name of the last sheet I copied the formula to. Why do you think I am getting this problem. Thanks in advance. Zafar
I will make a video on this. If you're using Microsoft 365, you can use this formula (where B1 and the year number and B2 has the month number): =SORTBY(IF(WEEKDAY(DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),2)>5,DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),""),IF(WEEKDAY(DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),2)>5,DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),""))
Hi, If i follow your steps and start with the find function, i'm not able to select the cell where it needs to find the "]" I type =FIND("]", and from that point i can't select the cell, i get error: There's a problem with this formula. Not trying to type a Formula? When the first character is ... and i don't know what is wrong with it. Even copying the Formula and pasting it doesn't let me show the sheet name (for example this Formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) Is there somewhere a setting that needs to be changed? My office is up to date to the latest version, i use Office 365 with subscription Thanks in advance. Greetings Nick
I am having the same problem as Vicki, each time I copy the formula to a new worksheet, it changes the formula in all previous entries to that of the last copied formula, - that is - if I initially enter the formula in Sheet1, then copy the formula in worksheet Sheet2, this changes the result in Sheet1 to Sheet2
Unfortunately it does that. The formula is dynamic and picks up the sheet name that is currently active, A workaround would be to hit the F9 key and it will force the formula to recalculate and give you the correct sheet name. Another, not so easy, solution would be to do it using VBA, where as soon as you change the sheet, it will force a recalculation
@@trumpexcel Unfortunately the workaround does not really fix the problem if you want to use the formula as headers that share information from the same workbook
@@benjamincaruana2964 I also faced the same issue but I got this alternate way which gets the tab name of the respective sheet. In a blank space simply paste this formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Hi, sir, good day, need your kind favor and assistance, I want to edit the sheet tab name (primarily sheet1) as per cell value in A1 as an example if i change the value in "A1" as April 6, 2014, it should change, but it's not working it always show the current date of the pc, why? I'm using the code shown below.... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then ActiveSheet.Name = ActiveSheet.Range("A1").Value ActiveSheet.Name = Format(Date, "DD-MM-YYYY") End if End Sub Any assistance if of great help. Many Thanks..
If I follow your gudie I get a error message from Excel. To make it work I had to use =CELL("filename") in the data cell and this formula in the results cell =TEXTAFTER(A73;"]")
I found the problem (and looking back at your video, I see it got you also, but you didn't notice) -- the formula comes back with the sheet name for the LAST place you pasted the formula -- on all the sheets. Not a workable solution!!!
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
First of all thanks a lot, I've benefitted a lot from your videos, it has eased my work.
As you've covered a lot in excel.
Can you also teach us DAX measures
Please share videos on creating dashboards
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
This formula will actually get the sheet name and not the ACTIVE sheet name as in the video (causing all sheets to show the same sheet name)
@Paul Thank you! You solved the ultimate problem with the original video/formula.
Exactly! I had quite an issue with this
Well what the maker of this video forgot to mention is that in the CELL formula you should simply add a reference cell of the current sheet. So "A1" is a good reference. This means that his formula should also work but edited. It would look like this: =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename")))
Exactly what I was looking for. once the formula in the video change when I used data validation.
@@micha-elvanluijtelaar3331 ADDING A CELL NAME WORKED! THANK YOU!!!
Excellent زبردست. From Pakistan
Nice job. Easy to understand. Thank you!
Great one Sumit! Thanks for demonstrating. Thumbs up!!
dude ur the best, u earned a subscriber
Hi, TE. Very clever. Thanks for sharing! Stay Healthy!
Wow nice, i can think of many ideas just from this hint... Many thanks sir
Glad you found the video useful :)
Clever! Please answer some of the questions posed. My exact problem is for totalling data from monthly sheets into a summary sheet. I want to be able to copy/paste (say) January's data into the other 11 months' totals rows, referencing each month. Sumit's solution still does not let me do this.
Great tutorial Sumit. Thank you!!
Really awesome 😇😎
Perfect trick when we use multiple worksheet in a workbook👍👌
Hi! Thank you! Your video is awesome! I had try it and it works on my computer. However, after I put it on office 365 the formula break. Can you please advice is it possible to do it for 365?
Great video..thank you for sharing this
Thank you for all you do.
very useful Sumit.
such a helpful and easy to follow video. thank you so so much!
Very informative and useful...
helpful, thanks a lot!
Thank you so much for the great excel trick.
Thanks Shiffa... Glad you liked it :)
Superb sir ❤❤❤
Thank you 😊
helpful, thankyou 👍
Kudos Sir
Thank you for such useful video. One of my summary file is connected to multiple source files kept in shared drive. Vales in these source files keep on changing daily basis. So I used '=' in my summary file to bring the values from source files. The problem is that every time I have to edit each link to derive the new values even after changing the settings under option. Update option under Data > edit links doesn't work. Sometimes it shows warning. Would be great to have your suggestions. Thanks
Hi Sumit Bansal Sir,
Thanks for the above useful video tutorial.
I am using the above formula described in the video, I get same sheet name in all sheets of the workbook.
Every time I have to refresh the cell to get the correct sheet name.
Any suggestions to resolve this problem.
Great! This is faster than putting it in Custom Footer in the Page Setup dialog box. Thank you!
Thank you Trump excel for sharing your knowledge with us
Thanks Dear
The way you nest functions is amazing🔥
Thanks Sujith... Glad you found the video useful :)
Thank you for the clear tutorial. I've learned a lot from your videos!
You have great timing as I am trying to do this very thing in a current workbook. I'm having trouble though. Works perfectly on Sheet1, but when I paste into Sheet2, it changes the name on both Sheet2 and Sheet1 to "Sheet2". Subsequently, when I save in Sheet3, it changes the names on all three worksheets to "Sheet3", and again with Sheet4, now they all have "Sheet4" as the name in the cell. It looks like the same thing happened in your example, when you changed from tab "Q4 2022" back to tab "Q1 2021" the name showing on Q1 tab is "Q4 2022".
Is it supposed to do that? Is there a way to prevent changing the names on the previous sheets? Thank you!
Unfortunately it does that. The formula is dynamic and picks up the sheet name that is currently active, A workaround would be to hit the F9 key and it will force the formula to recalculate and give you the correct sheet name. Another, not so easy, solution would be to do it using VBA, where as soon as you change the sheet, it will force a recalculation
@@trumpexcel I'm good with F9. Not ready for VBA, yet. Thank you for the reply!
I had this problem too. One way I found to fix it was by adding a [reference] in the CELL() function:
=TEXTAFTER(CELL("filename",A1),"]")
where A1 can refer to any cell on that particular sheet.
Hope this helps!
@@danielcastanon1706 THIS is exactly what I've been looking for! Thank you :)
Sumit, question.
I have an excel workbook with a summary sheet and other worksheet names as dates of a month (06 or 17 or 22 and so on). In the summary sheet, I want to pull data from sheets of a specific date. For ex in the summary sheet, if I use the date 06 on cell A2, then summary sheet B2 should show a value from sheet 06 a4. How can I achieve this?
Thank you for helping me out with this.
very good trick, perfectly explained.
Thanks Thomas... Glad you liked the video :)
Dear Sumit, thanks for your excellent videos...I have learnt a lot from them. I am having a small issue when I use your formula for getting the sheet names. The formula works very nicely, but if I copy it to other sheets in the same workbook, all worksheets change to display the name of the last sheet I copied the formula to. Why do you think I am getting this problem. Thanks in advance. Zafar
Zafar: Provide a reference cell (any blank ell) in your formula. I think this is what is missing.
Hey how about if it's vice versa?
Like the name of the sheet can be changed through cell data
Good one, thanks. Do you know the formula to get all the sunday date of January 2022 for eg (2,9.16,23 & 30) in different cell?
I will make a video on this. If you're using Microsoft 365, you can use this formula (where B1 and the year number and B2 has the month number): =SORTBY(IF(WEEKDAY(DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),2)>5,DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),""),IF(WEEKDAY(DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),2)>5,DATE(B1,B2,SEQUENCE(EOMONTH(DATE(B1,B2,1),0)-DATE(B1,B2,1)+1)),""))
@@trumpexcel Thanks, its working but it is giving saturday date as well. I need only sundays date.
Can I also use this in google sheet?
Cool thanks
Thanks
Great Video, Sumit 👍
May be LET() helps us to reduce the formula size by keeping CELL() as variable.
It is useful
very good tutorial
Hi,
If i follow your steps and start with the find function, i'm not able to select the cell where it needs to find the "]"
I type =FIND("]", and from that point i can't select the cell, i get error:
There's a problem with this formula.
Not trying to type a Formula?
When the first character is ...
and i don't know what is wrong with it.
Even copying the Formula and pasting it doesn't let me show the sheet name
(for example this Formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Is there somewhere a setting that needs to be changed?
My office is up to date to the latest version, i use Office 365 with subscription
Thanks in advance.
Greetings Nick
Hi, I'm having 42 sheets while I have to copy and paste data of 42 sheets in one sheet, can you explain how I can execute????
How will get the All these tabs name in one sheet or 1 column ?
I am having the same problem as Vicki, each time I copy the formula to a new worksheet, it changes the formula in all previous entries to that of the last copied formula, - that is - if I initially enter the formula in Sheet1, then copy the formula in worksheet Sheet2, this changes the result in Sheet1 to Sheet2
Unfortunately it does that. The formula is dynamic and picks up the sheet name that is currently active, A workaround would be to hit the F9 key and it will force the formula to recalculate and give you the correct sheet name. Another, not so easy, solution would be to do it using VBA, where as soon as you change the sheet, it will force a recalculation
@@trumpexcel Unfortunately the workaround does not really fix the problem if you want to use the formula as headers that share information from the same workbook
@@benjamincaruana2964 I also faced the same issue but I got this alternate way which gets the tab name of the respective sheet. In a blank space simply paste this formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
@@reshmag7825 Thanks a lot.
Your Comment was way more useful than this whole tutorial.
@@reshmag7825 thanks dude!!!
Look at 3.31 - You have switched to sheet Q1 2001, but the formula is showing up the value from the previous sheet: Q4 2022. I have same bug.
Does this work in googlesheet?
Hi, sir, good day, need your kind favor and assistance, I want to edit the sheet tab name (primarily sheet1) as per cell value in A1 as an example if i change the value in "A1" as April 6, 2014, it should change, but it's not working it always show the current date of the pc, why? I'm using the code shown below....
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = ActiveSheet.Range("A1").Value
ActiveSheet.Name = Format(Date, "DD-MM-YYYY")
End if
End Sub
Any assistance if of great help. Many Thanks..
Not working in excel 2013 provide solution
If I follow your gudie I get a error message from Excel. To make it work I had to use =CELL("filename") in the data cell and this formula in the results cell =TEXTAFTER(A73;"]")
but if use cell faction show only one worksheet name in a excel file
Just use
=RIGHT(CELL("filename"), number of sheet name characters)
When I use this, it refers to just one sheet only when I move to other sheets i.e. It'll only show the name of the first sheet.
how i get these sheet name in one sheet
When I copy the final formula to the other sheets all the sheets are named the same.
Why your not using MID formula?
You can also use MID, both formulas work
this wont work if your sheet names are different lenght... :(
Why not just use =textafter(cell("filename"),"[") WAYYYYYYYY faster
Thank you!
Not working
👏
I found the problem (and looking back at your video, I see it got you also, but you didn't notice) -- the formula comes back with the sheet name for the LAST place you pasted the formula -- on all the sheets. Not a workable solution!!!
This formula will not work when you create a copy of the sheet in same workbook
with new office 365 functions --- even shorter formula... =TEXTAFTER(CELL("filename"),"]")
how to calculate number of sheets in a workbook(excel file)
=sheets()
Not work