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
@sujithortan5773
2 жыл бұрын
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
@shaileshcastelino6950
2 жыл бұрын
Please share videos on creating dashboards
@paulmarsman8758
Жыл бұрын
=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)
@vgonyea4958
Жыл бұрын
@Paul Thank you! You solved the ultimate problem with the original video/formula.
@patrykmakowski3409
Жыл бұрын
Exactly! I had quite an issue with this
@micha-elvanluijtelaar3331
Жыл бұрын
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")))
@williamtomlinson4801
Жыл бұрын
Exactly what I was looking for. once the formula in the video change when I used data validation.
@mikemyers8811
10 ай бұрын
@@micha-elvanluijtelaar3331 ADDING A CELL NAME WORKED! THANK YOU!!!
@wayneedmondson1065
2 жыл бұрын
Great one Sumit! Thanks for demonstrating. Thumbs up!!
@mohammadilyas2240
2 жыл бұрын
Excellent زبردست. From Pakistan
@danielhartjes2479
Жыл бұрын
Nice job. Easy to understand. Thank you!
@nareshnautiyal2825
2 жыл бұрын
Perfect trick when we use multiple worksheet in a workbook👍👌
@jonminer9891
2 жыл бұрын
Hi, TE. Very clever. Thanks for sharing! Stay Healthy!
@oemarprawirosadi5299
2 жыл бұрын
Wow nice, i can think of many ideas just from this hint... Many thanks sir
@trumpexcel
2 жыл бұрын
Glad you found the video useful :)
@IvanCortinas_ES
2 жыл бұрын
Great tutorial Sumit. Thank you!!
@tarentrew4693
Жыл бұрын
such a helpful and easy to follow video. thank you so so much!
@The_1two3
2 жыл бұрын
Really awesome 😇😎
@didotbasmayor
Жыл бұрын
dude ur the best, u earned a subscriber
@sherrycohen1824
2 жыл бұрын
Great! This is faster than putting it in Custom Footer in the Page Setup dialog box. Thank you!
@sujithortan5773
2 жыл бұрын
The way you nest functions is amazing🔥
@trumpexcel
2 жыл бұрын
Thanks Sujith... Glad you found the video useful :)
@abiodunonadeji7549
2 жыл бұрын
Thank you for all you do.
@sandeepgupta9498
2 жыл бұрын
helpful, thanks a lot!
@padmasirisrinarayana2293
Жыл бұрын
Thanks Dear
@pyinoolwin1000
Жыл бұрын
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.
@banerjeenandan
2 жыл бұрын
Very informative and useful...
@shaileshcastelino6950
2 жыл бұрын
Great video..thank you for sharing this
@shiffamohammed5818
2 жыл бұрын
Thank you so much for the great excel trick.
@trumpexcel
2 жыл бұрын
Thanks Shiffa... Glad you liked it :)
@prashantsirvya8563
Ай бұрын
very useful Sumit.
@IQ_Investment
2 жыл бұрын
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
@nadermounir8228
2 жыл бұрын
Thank you Trump excel for sharing your knowledge with us
@HomeChang-by8gp
3 ай бұрын
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?
@arun4846
7 ай бұрын
Superb sir ❤❤❤
@trumpexcel
7 ай бұрын
Thank you 😊
@jessipinkman7659
2 жыл бұрын
Kudos Sir
@komalwadhwa1910
7 ай бұрын
helpful, thankyou 👍
@calilthomas
2 жыл бұрын
very good trick, perfectly explained.
@trumpexcel
2 жыл бұрын
Thanks Thomas... Glad you liked the video :)
@vickievans5911
2 жыл бұрын
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!
@trumpexcel
2 жыл бұрын
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
@vickievans5911
2 жыл бұрын
@@trumpexcel I'm good with F9. Not ready for VBA, yet. Thank you for the reply!
@danielcastanon1706
10 ай бұрын
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!
@melissaallisonwood
4 ай бұрын
@@danielcastanon1706 THIS is exactly what I've been looking for! Thank you :)
@zafarrizvi8676
2 жыл бұрын
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
@gunvantsoni4697
Жыл бұрын
Zafar: Provide a reference cell (any blank ell) in your formula. I think this is what is missing.
@TheExcelism
2 жыл бұрын
Great Video, Sumit 👍 May be LET() helps us to reduce the formula size by keeping CELL() as variable.
@DavidWilliams-wj4sc
7 ай бұрын
Why not just use =textafter(cell("filename"),"[") WAYYYYYYYY faster
@Maritime_History
4 күн бұрын
Thank you!
@bahersedrak6616
2 жыл бұрын
Thanks
@enadio9398
2 жыл бұрын
Cool thanks
@BinodKumar-de8ch
Жыл бұрын
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.
@sumantaimd
2 жыл бұрын
very good tutorial
@canirmalchoudhary8173
2 жыл бұрын
It is useful
@Dany-ns6hg
2 жыл бұрын
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?
@trumpexcel
2 жыл бұрын
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)),""))
@Dany-ns6hg
2 жыл бұрын
@@trumpexcel Thanks, its working but it is giving saturday date as well. I need only sundays date.
@sweetmask1690
6 ай бұрын
Hey how about if it's vice versa? Like the name of the sheet can be changed through cell data
@benjamincaruana2964
2 жыл бұрын
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
@trumpexcel
2 жыл бұрын
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
@benjamincaruana2964
2 жыл бұрын
@@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
@reshmag7825
2 жыл бұрын
@@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)
@balveersingh01
2 жыл бұрын
@@reshmag7825 Thanks a lot. Your Comment was way more useful than this whole tutorial.
@chemanork
Жыл бұрын
@@reshmag7825 thanks dude!!!
@AlexKW
Жыл бұрын
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.
@MarieFrancisco-o4g
Жыл бұрын
Can I also use this in google sheet?
@СтепанДмитриевичКонев
Жыл бұрын
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.
@DhruvGoswamiOfficial
2 жыл бұрын
Not working in excel 2013 provide solution
@erikguzik8204
2 жыл бұрын
with new office 365 functions --- even shorter formula... =TEXTAFTER(CELL("filename"),"]")
@mohammaddanish5608
Жыл бұрын
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????
@mohdsartaj9554
Жыл бұрын
How will get the All these tabs name in one sheet or 1 column ?
@KFN_VII
Жыл бұрын
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.
@simonfredriksson5396
Жыл бұрын
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;"]")
@vsayikiran
Жыл бұрын
Does this work in googlesheet?
@Meerschaut87
Жыл бұрын
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
@MarkGephart-c6v
8 ай бұрын
When I copy the final formula to the other sheets all the sheets are named the same.
@Atulrathihts
Жыл бұрын
but if use cell faction show only one worksheet name in a excel file
@BartoszXIV
Жыл бұрын
this wont work if your sheet names are different lenght... :(
@mauricelee2219
Жыл бұрын
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..
@khalidmajeed2886
2 жыл бұрын
how i get these sheet name in one sheet
@amolkadam346
2 жыл бұрын
Why your not using MID formula?
@trumpexcel
2 жыл бұрын
You can also use MID, both formulas work
@mrshabeermp
2 жыл бұрын
👏
@JonFreivald
10 ай бұрын
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!!!
@ExcelHubPro
2 жыл бұрын
This formula will not work when you create a copy of the sheet in same workbook
@zoharbatterywala1974
2 жыл бұрын
how to calculate number of sheets in a workbook(excel file)
Пікірлер: 94