I was looking up hyperlink index in Word to see if it existed and I found this video! Excellent! I've wanted to create a table of contents in excel for years, I didn't know how to do it, and I didn't think the feature existed. Thank you so much for this video! Very clear. I'm anxious to try it.
@Karina. This tutorial was clear, understandable and easy to follow. I appreciate this tutorial. My 2023 workbook finally has a TOC with links. Many thanks to you.
This was f......... fantastic! I have an Excel sheet with lots of tabs for chess opening notes (ECO code classifications go from A00 to E99) on various openings for both white and black, and within each opening there are a number of sub variations. I will be able to navigate my workbooks MUCH, MUCH more easily now! A huge thank you Karina! :0)
Hi Karina, This was a brilliant example of how well a short training video can be, clear, simple, even for me ;-) and concise... the bit I loved the best was you described what sooo sooo many others seem to fail time again to do and that is the thought process as you are working through (what has until now eluded me) and that was the step by step explanation of each piece of the syntax in the hyperlink and what it does. I'm comfortable working with PQ but for the life of me it never occurred to me to use it in this manner, fantastically brilliant. I tell you this really put a smile on my face, if only you could see.😀 this is a poor imitation but you get the point. I've been labouring over different ways to do this for ages and I make a lot of 'Excel Workbooks' so this tidbit is invaluable. So using PQ to generate the list of sheets was a great step but the explanation of the hyperlink was the icing on the cake. I've seen it before but "no one" bothers to explain what the significance of each part of the hyperlink formula meant. The bit I've strangely never found answer to was what the # symbol did in this context, maybe I just didn't search hard enough..... so thank you, thank you, thank you for making one of my main jobs just so so much easier.. 😃😃😃 Kind regards David
Some additional follow up observations and clarification. The clarification is that I am working on Windows 10 using MS Office 365 (on a Macbook Pro via Parallels) not that I believe the later should be of any concern but annoyingly... I cannot get it to work... how bizarre. So upon a little further testing it seems like working on a Mac even running Windows 10 via Parallels yields some 'oddities' especially when it comes to file paths etc.... who knew, not me certainly, well you alway learn something new everyday if you just look ;-) I tried a simple experiment, I used the normal, right click create "HYPERLINK" (excel 2016, my normal excel at work) but on MS Office 365 it is "LINK" When I compared the link path it was look at I was surprised, it was pointing to file:///\\Mac\Home\Documents\Workbook_Template.xlsm oooohh that's nasty. So I'm guessing somewhere in the internal workings of using the usual =HYPERLINK("#'"&[@Name]&"'!A1",[@Name] sort of gets messed up... I'll have to wait until tomorrow and test it at work to see the difference but I've no doubt that it's a good solution. Hope the insight my help anyone else who tries something similar.
This was an EXCEL-ENT tutorial!! Thank you for creating this video, it gave me a basic start to creating a table of contents. I have questions which is what made this a very good learning experience! 💖
I try doing this but i get the message "The Process Cannot Access The File Used by Another Process". Could that be due to the file existing in a cloud service? I followed the steps exactly, so if the cloud isn't the issue im not sure what would be. help!
This video is so perfectly made! The info is succinct, yet clear; the cadence is spot on, and the length is perfect. However, I did run into some issues, I could not do this while in the workbook. I had to close the workbook, open a blank one, then run the Power Query. I also do not have "Remove other columns" I assume I am running a different version of Excel. I attempted using this formula and it only copied the cell within the sheet I was in.
I am wondering if, when adding a new tab, it will appear in the existing table of contents, or if this process needs to be repeated whenever a new tab is added?
Thanks for this. It is possible to make the Contents list "dynamic" so that when you add a new sheet, it will be automatically added to the Contents sheet?
Hi Karina, first of all, thanks for this helpful video! Can I know when we wish to insert more tabs in this file, will this table of contents updated automatically too? For example, I need to create about 10 new tabs per month in this Excel file. How can the table of contents include the new 10 tabs in the list, please?
Hi Karina, one more question, in the table of contents, can I know if we can base on the name of the tab to equal to certain column in other tabs? For example: in the table of contents of your excel file, I want the insert 1 more column at the right to have the value showing at D100 of each tab. Instead of manually equal it individually, can I know if we have any formula to equal it on the spot, please? Please let me know if I cannot express myself well..... Thanks!
I was looking up hyperlink index in Word to see if it existed and I found this video! Excellent! I've wanted to create a table of contents in excel for years, I didn't know how to do it, and I didn't think the feature existed. Thank you so much for this video! Very clear. I'm anxious to try it.
@Karina. This tutorial was clear, understandable and easy to follow. I appreciate this tutorial. My 2023 workbook finally has a TOC with links. Many thanks to you.
This was f......... fantastic! I have an Excel sheet with lots of tabs for chess opening notes (ECO code classifications go from A00 to E99) on various openings for both white and black, and within each opening there are a number of sub variations. I will be able to navigate my workbooks MUCH, MUCH more easily now! A huge thank you Karina! :0)
Wow, great, simple idea and use of Power Query Karina!
Hi Karina,
This was a brilliant example of how well a short training video can be, clear, simple, even for me ;-) and concise... the bit I loved the best was you described what sooo sooo many others seem to fail time again to do and that is the thought process as you are working through (what has until now eluded me) and that was the step by step explanation of each piece of the syntax in the hyperlink and what it does. I'm comfortable working with PQ but for the life of me it never occurred to me to use it in this manner, fantastically brilliant. I tell you this really put a smile on my face, if only you could see.😀 this is a poor imitation but you get the point. I've been labouring over different ways to do this for ages and I make a lot of 'Excel Workbooks' so this tidbit is invaluable. So using PQ to generate the list of sheets was a great step but the explanation of the hyperlink was the icing on the cake. I've seen it before but "no one" bothers to explain what the significance of each part of the hyperlink formula meant. The bit I've strangely never found answer to was what the # symbol did in this context, maybe I just didn't search hard enough..... so thank you, thank you, thank you for making one of my main jobs just so so much easier.. 😃😃😃
Kind regards
David
Some additional follow up observations and clarification.
The clarification is that I am working on Windows 10 using MS Office 365 (on a Macbook Pro via Parallels) not that I believe the later should be of any concern but annoyingly... I cannot get it to work... how bizarre.
So upon a little further testing it seems like working on a Mac even running Windows 10 via Parallels yields some 'oddities' especially when it comes to file paths etc.... who knew, not me certainly, well you alway learn something new everyday if you just look ;-)
I tried a simple experiment, I used the normal, right click create "HYPERLINK" (excel 2016, my normal excel at work) but on MS Office 365 it is "LINK"
When I compared the link path it was look at I was surprised, it was pointing to file:///\\Mac\Home\Documents\Workbook_Template.xlsm oooohh that's nasty.
So I'm guessing somewhere in the internal workings of using the usual =HYPERLINK("#'"&[@Name]&"'!A1",[@Name] sort of gets messed up...
I'll have to wait until tomorrow and test it at work to see the difference but I've no doubt that it's a good solution.
Hope the insight my help anyone else who tries something similar.
This was an EXCEL-ENT tutorial!! Thank you for creating this video, it gave me a basic start to creating a table of contents. I have questions which is what made this a very good learning experience! 💖
I try doing this but i get the message "The Process Cannot Access The File Used by Another Process". Could that be due to the file existing in a cloud service? I followed the steps exactly, so if the cloud isn't the issue im not sure what would be. help!
I got this message also.
@@kathiesandburg8339 Did you find a workaround?
Same issue here. Frustrating
Very helpful. Thank you. Especially for the Hyperlink function.
Thank you so much for the excellent guidance. Best regards
Thank you so much! This was really helpful!
This video is so perfectly made! The info is succinct, yet clear; the cadence is spot on, and the length is perfect.
However, I did run into some issues, I could not do this while in the workbook. I had to close the workbook, open a blank one, then run the Power Query.
I also do not have "Remove other columns" I assume I am running a different version of Excel.
I attempted using this formula and it only copied the cell within the sheet I was in.
is there a way i could use cell reference to call data from cell C3 from all sheets next to the hyperlinks created in the TOC?
Outstanding! Thank you!! 👏🏾👏🏾👏🏾
Thank you so much for this. Good job and keep it up.
But what happens if you add new sheets or delete them. Does it auto change?
it does not. I just tested it. Might have to re-run the query or make a larger source selection. I am looking for answers too.
I was almost asking the same.
I am wondering if, when adding a new tab, it will appear in the existing table of contents, or if this process needs to be repeated whenever a new tab is added?
Thanks for this. It is possible to make the Contents list "dynamic" so that when you add a new sheet, it will be automatically added to the Contents sheet?
Can anyone advise how to enable "Get Data" button under Data? I can't find it in my Macbook Pro. I'm using Office Home & Student
2021. Thank you.
Hi Karina
Can not get the hyperlink to work, keep getting an error.
how do you update the data/table, having addedd new worksheets to the workbook, without having to start the process again?
Very useful! Thank you!
Thankyou for this, however i encountered a problem saying it is being used in another process can you help me fix this please
Hi Karina, first of all, thanks for this helpful video!
Can I know when we wish to insert more tabs in this file, will this table of contents updated automatically too? For example, I need to create about 10 new tabs per month in this Excel file. How can the table of contents include the new 10 tabs in the list, please?
Hi Karina, one more question, in the table of contents, can I know if we can base on the name of the tab to equal to certain column in other tabs?
For example:
in the table of contents of your excel file, I want the insert 1 more column at the right to have the value showing at D100 of each tab.
Instead of manually equal it individually, can I know if we have any formula to equal it on the spot, please?
Please let me know if I cannot express myself well..... Thanks!
Hi Karina. Thanks for sharing this. Is this process automated? if I change the tables, will the table of contents update automatically?
Hi, I made the table of contents as you taught. It shows the hyperlink but not taking me to the specific sheet when I click on any name. please guide
This is quite useful! But does anyone know how to keep the table of content active when you export the complete document to PDF?
Fantastics
What happens if another sheet is added, how to auto update the table of contents?
Fantastic!
Awesome, thank you!
thank you! Great help
ONE CAN MAKE THE TOC IN A BREEZE...THANKS DEAR:-)
🔝🔝🔝
voice qulity is really bad use good speakers
time wasted