Extract a List of Worksheet Names Using Functions - With 2 ways Navigation
HTML-код
- Опубликовано: 3 авг 2024
- In this tutorial I show you how to create a list of All sheet names in any workbook by using a Macro language function (=Get.Workbook) inside a Defined name. I will then combine the Defined name with an INDEX function, a ROW function, a REPLACE function and an IFERROR function.
Then we create a Forward and Backward Navigation from the Index of Sheets to any other sheet and back, using a HYPERLINK function.
You can Download the Start File and follow along by clicking on the link:
www.amazon.ca/clouddrive/shar...
There is a quiz Question for you to answer at 9:00 min... give it a try and write your answer in a comment.
There are two other methods for creating a List of Sheet Names, either by using a VBA code or by using Power Query. I sincerely encourage you to watch these 2 other methods and write in a comment, which of the 3 methods you like most. Here are the links to the other methods:
Tutorial for Using Power Query
• Create a List of Sheet...
Tutorial for Using a VBA code
• Simple VBA Code to cre...
If you find value in this tutorial give it a thumb up, and hit the “Subscribe” button to be notified when new videos are posted.
When Nabeel explains the thing, it becomes easier either of all the ways!!!
Commendable 👏👏👏
I have 4 different solutions for the quiz question:
1) By using the same index function we created with the same defined name;
=COUNTA(INDEX(MySheets,0))
2) We can define a new name via name manager similar to before. Let's call it as "CountSheets" coming with formula Get.Workbook(4)
=INDEX(CountSheets,0)
3) Select any cell you want, type "=CountSheets" formula and then hit Enter =)
4) The formula below also gives the count of total number of sheets in the workbook as well;
=Sheets()
By the way, your whole tutorials are really great and I'm grateful for that. Thank you very much ;-)
Awesome video. VERY well explained. I know this video is a few years old, just some update info for any Office 356 Users , here is how you can make the list that was created in Col(A) instead of manually copying the formula. It will also always show the number of sheet names you have. =TEXTAFTER(INDEX(MySheets,SEQUENCE(SHEETS())),"]")
Exceptionally useful for large spreadsheets with multiple worksheets. I have a very large Budget spreadsheet for our businesses and have been forever clicking back and forth between worksheets... this is so useful in saving time. Thanks for providing this.
I invite you to watch the other methods as well. (links below the video)
Wow beautiful way to teach! Thank u so much master for share your knowledge. God bless u more!
You are very welcome
Excellent. Very informative. Interesting, inspiring, revealing, thought provoking, realstic, lucid & candid..Thanks a lot.
Glad you like it.
I invite you to Watch my new tutorials
Very clear and concise explanation. Many thanks!
Mr. Nabil,
It is very nice to see your videos. I used to watch your all available updates. I like your way of presentation of the subject and specially your accent. Thanks for your all endeavors for educating one who loves the excel.
Great job
Shaniwafs
Many many thanks
wow! Good teacher, this has really help me. Thank you very much
Wonderfully put across: that was the best 15.43 minutes I have spent on RUclips. I am new to your channel and I am grateful for clear, step by step explanations. Thank you.
What a coincidence, I'm online and I read your comment almost in real time...
There are lots of amazing videos on my channel... I will be glad to have you as a subscriber... The Best Is Yet To Come
Its just amazing my friend, I am speechless. Thanks a lot
Your excel knowledge is amazing....
Thanks a ton
This is great! Thanks!
Awesome, very cool, very useful. Thank you!
Superb.. Much informative & useful..
Thanks for sharing.
My pleasure 😊
Very nice video. Thank you for your hard work 👍
Very good description ♥️🖤💜💜
Awesome. In my opinion PQ's method is best.
I agree
what if i have more than 38 worksheets .. how to fetch the names then? btw you are an amazing teacher
OUTSTANDING
Thank you
Very helpful. Thanks a lot. One request though. What do I have to do in order to have the list dynamically, in the sense that any new sheet tab that appears afterwards, the List of Sheets to update automatically?!
Very informative
Glad it was helpful!
Love
You're the best. Subscribed!
Thank you for the nice comment.
Show your support to my channel by sharing the video
This is just amazing. Is there a way to exclude hidden slides
Awesome !!!!!!!!!
Thank You for these tips.
Also wanted to know whether you can also add a column to show the status of the sheet eg; Visible or Hidden
Thank you once again
I might answer your question in a future tutorial... Stay tuned.... The Best Is Yet To Come
@@Officeinstructor Thank You
Really appreciated the feedback
cool tutorial !
how to make the list sheets horizontaly ?
Thanks for the video. It helped me a lot. But how can i get a cell value(say H8) from a sheet after getting the sheet name using this formula? Waiting for your valuable answer.
Wow
thanks for that. followed steps I managed to create links between different worksheets. but cant get automatic update to the list when I ad new worksheet or I change name of old ones. did I miss anything ? thanks
i want to create main worksheet with specific data and dates i.e court cause list. dates are changed/adjourned dates as required .if next date is added or change it may create automatic cause list on worksheet as per date ...and it collect data from main worksheet as per date entry. .your reply will be appreciated . thanks
I have a list of names in one sheet. I want to produce sheets that get the name from that list.
How can I do that?
You just need to Click right Click between the arrows in the corner of workbook beside of sheet bar
Thank you Abdullah... I already mentioned this option in my tutorials, but the purpose is to create a Table of Contents which you could then use in your project (not just see them) like use sheet names in other formulas or even print them or export for collaborative working ...beside we created Both ways navigation.