To get this to work if your sheets have spaces in the names you need to add a single quote before and after the reference to the cell name. So in Anser's example it would be =HYPERLINK(CONCAT("#","'",B2"'","!A1"),"Go To…"). The CONCAT resolves as =HYPERLINK(#'Sheet Name'!A1,"Go To...")
Totally superfluous. Excel automatically builds a list of worksheets in a workbook file. Use your mouse to hover over the extreme bottom left of your worksheet tabs and the left/right angle brackets. Right click and the list pops up so you can highlight and select the desired sheet. Use this method and you don’t garbage up the individual sheets with extraneous non-data related cells/rows/columns that can become corrupted, outlive their usefulness when a sheet is deleted, or need updating when a sheet’s tab is renamed.
For those of you having issues making this function properly, I have another video which shows a slightly different method to achieve this. Video Name: Instant Excel Sheet Navigation without VBA! - Using DATA VALIDATION, INDIRECT, and ADDRESS Functions Video Link: ua-cam.com/video/T_YcXR2HcqI/v-deo.html&lc=UgxhrS92A3lTUgOtK7N4AaABAg
Thank you for this. I've tried multiple ways to get to my overwhelming amount of tabs. I love the idea of the macro to get back to the TOC with the keystrokes! Do you know a way to specify what window view the tab opens in? I've tried Microsoft and they suggested java and the API which I'm not currently an expert at. I use multiple windows of the same workbook and the behavior of opening a selected tab to a random window is beyond problematic when I'm collecting data from one window for another and I never know which window will be replaced by my tab selection.
If I understand correctly, you want to select a sheet within your workbook, and you want a new window to open with the selected sheet. To keep it simple, one possible solution may be to go to View -> New Window. Then, use than duplicate window to do all your navigation. This way, you will always have your main Excel sheet open, and both windows should remain in sync as well.
Yes. Or for a quicker, simpler way: right-click the cell you want the TOC to be and select Link, then under "Link to:" click on "Place in this Document", and click the name of the sheet you and to create a Hyperlink to. [total of 4 clicks] After that first one, it will only take 3 clicks to create each Hyperlink thereafter.
If you need to prepare a report in excel with many sheets and send it people that have little knowledge of excel, they won’t realise there is such shortcut, so a table of content with links is better. Also it’s good to add a link in each sheet that would take you back to TOC.
Very helpful information Anser. Now that I've got my ToC set up, I'd like a similar solution to getting back to the ToC from distant worksheets without linking each by hand. Also, why are the names of the work sheets in blue and underlined; are they already linked to something?
Hi, the names of the worksheets are just formatted blue and underlined, that's why. They are not linked to anything. I'd recommend you check out my recent video on this, where I show how to make a drop-down list from which you can select which sheet to navigate to. As for getting back to TOC, go to any one of the spreadsheets, in the developer tab, record a macro. Give the macro a shortcut, such as CTRL + w. Then, start recording the macro. From your active sheet, go to sheet 1 (the TOC). After this, stop recording. Now, whichever sheet you're on, you simply press ctrl + w to get back to toc. I tried this method, and it took only a couple seconds.
@@ExcelWithAnser Thank you so much for the prompt response, Anser. The macro answer is perfect, and I can create an icon for my quick-access toolbar. You rock!
Good point; you can do that. When you create a hyperlink to go back home on one spreadsheet, you can simply create a macro to generate the same hyperlink for all sheets.
Formula 1) =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) Formula 2) = INDEX(ListWorksheets, A2) (remember “listworksheets is the custom formula I made in name manager, and I named it this) Formula 3) =HYPERLINK(CONCAT(“#”,B2,”!A1”), “Go to sheet”)
Thanks - really likes what this formula can do - however using Excel for Mac the "GET.Worksheet" formula does not exist - is there a work around to get this to work on a Mac also, thanks
HI, the name of my sheets content - or _ (ex DNR-772 or 775_23) and the hyperlink function is not working, How can I improve it? I noticed the sheets without those caracters works fine
I may be slow on the uptake, but when I copy and paste your REPLACE formula into my Create New Function pop-up in Excel, it tells me there's a problem with the formula. It seems to zero in on the "]" bit as the part it doesn't like. Thoughts? I hope I can get this thing to work because I have a pretty massive workbook. This would really make it easier to navigate! Thanks for any counsel.
I doubt this will work, but try replacing the square bracket with a round one. Otherwise, you can right-click the bottom left of your Excel sheet, on top of the sheet navigation arrows, this will open up a list of all sheets within your workbook, and you can navigate from there.
Very good question. I do not have an answer for you, but that is something I will consider for a future video. In the meantime, maybe you should look into recording a macro that could get you to the homepage using a keyboard shortcut.
Because you may not be using the same cell references as I did for my worksheet. You will have to adjust the formulas based on your specific worksheet. For example, I may have something in cell A1 of my sheet, and you may have the same thing in cell B7 of your worksheet. Also, when I made the custom formula in Name Manager, I called in "ListWorksheets", and I used that name to link to a formula. If you didn't use the same name, then your formula will be different as well.
@@ExcelWithAnser Anser! I had to rename my sheets in the workbook to eliminate spaces! Should’ve thought about that before thank you for the reply tho and the video very useful!
Anser, when I try to set up the TOC with the name mgr. I keep getting #NAME# in the cell where the =replace function is placed and cannot get TOC to come up so I can copy down the sheet - solution??
Yes, I observed this error as well, and I'm unsure why it occurs. Please stay tuned for a future video, perhaps with a better alternative. In the meantime, I'd recommend you right click the bottom left of your worksheet to navigate from there.
When I set up the Define Name in Name Mgr. with the 'list worksheet' and place the =replace in the right area, it does not appear to save in a list as your example does in the youtube instruction and I feel there is a glitch in this area when gong to set up TOC sheet - this is disappointing as I feel that this TOC set up is EXACTLY what am looking for having many spreadsheet files set up and this process is going to make organizing a little easier.
I agree, this is disappointing, especially because you are doing the exact same steps. Have you tried using another PC, perhaps there is a Windows/Mac or Excel Version discrepancy? @@jefffaulkner4492
@@ExcelWithAnser Thanks for your reply.... I have removed all the spaces from the worksheet name but still I am getting #NAME? error in Index formula... please help... thank you
@@yashalinavalkar7354 Whatever you name your function in the Name Manager, that should be the name in your Index function as well. For example, when I created a new name in Name Manager, I called it "ListWorksheets". I then used this exact name in my Index formula. Also, now that you have changed the names of all worksheets to remove spaces, try this process once again, from the beginning.
Formula 1) =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) Formula 2) = INDEX(ListWorksheets, A2) (remember “listworksheets is the custom formula I made in name manager, and I named it this) Formula 3) =HYPERLINK(CONCAT(“#”,B2,”!A1”), “Go to sheet”)
Hi, =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) A few other formulas were mentioned; please watch the video for those formulas, and tailor them to your spreadsheet accordingly.
To get this to work if your sheets have spaces in the names you need to add a single quote before and after the reference to the cell name. So in Anser's example it would be =HYPERLINK(CONCAT("#","'",B2"'","!A1"),"Go To…"). The CONCAT resolves as =HYPERLINK(#'Sheet Name'!A1,"Go To...")
Good point
Totally superfluous. Excel automatically builds a list of worksheets in a workbook file. Use your mouse to hover over the extreme bottom left of your worksheet tabs and the left/right angle brackets. Right click and the list pops up so you can highlight and select the desired sheet.
Use this method and you don’t garbage up the individual sheets with extraneous non-data related cells/rows/columns that can become corrupted, outlive their usefulness when a sheet is deleted, or need updating when a sheet’s tab is renamed.
For those of you having issues making this function properly, I have another video which shows a slightly different method to achieve this.
Video Name: Instant Excel Sheet Navigation without VBA! - Using DATA VALIDATION, INDIRECT, and ADDRESS Functions
Video Link: ua-cam.com/video/T_YcXR2HcqI/v-deo.html&lc=UgxhrS92A3lTUgOtK7N4AaABAg
I cannot get your formula to work... I copied and pasted it and copied it from a picture and hand wrote it
@@katherinep4210 Try my method
thank you, handy! what if you envelop with HYPERLINK the first column..? the idea is great! 👏👏
That could be a viable alternative. I'd love to hear the results from you if you try it!
Would I be able to open hidden sheets with this formula?
Thank you for this. I've tried multiple ways to get to my overwhelming amount of tabs. I love the idea of the macro to get back to the TOC with the keystrokes! Do you know a way to specify what window view the tab opens in? I've tried Microsoft and they suggested java and the API which I'm not currently an expert at. I use multiple windows of the same workbook and the behavior of opening a selected tab to a random window is beyond problematic when I'm collecting data from one window for another and I never know which window will be replaced by my tab selection.
If I understand correctly, you want to select a sheet within your workbook, and you want a new window to open with the selected sheet.
To keep it simple, one possible solution may be to go to View -> New Window. Then, use than duplicate window to do all your navigation. This way, you will always have your main Excel sheet open, and both windows should remain in sync as well.
thanks for the video but where is the formula in the descriptions?
Just right click the worksheet navigation arrow at the bottom. Ypu get a list of all the worksheets and can navigate directly to them!
Yes. Or for a quicker, simpler way: right-click the cell you want the TOC to be and select Link, then under "Link to:" click on "Place in this Document", and click the name of the sheet you and to create a Hyperlink to. [total of 4 clicks]
After that first one, it will only take 3 clicks to create each Hyperlink thereafter.
If you need to prepare a report in excel with many sheets and send it people that have little knowledge of excel, they won’t realise there is such shortcut, so a table of content with links is better. Also it’s good to add a link in each sheet that would take you back to TOC.
@@XAE_A_XiiInstead of garbaging up the sheets with excess links, teach those recipients who are ignorant of Excel’s built-in capabilities.
Very helpful information Anser. Now that I've got my ToC set up, I'd like a similar solution to getting back to the ToC from distant worksheets without linking each by hand. Also, why are the names of the work sheets in blue and underlined; are they already linked to something?
Hi, the names of the worksheets are just formatted blue and underlined, that's why. They are not linked to anything. I'd recommend you check out my recent video on this, where I show how to make a drop-down list from which you can select which sheet to navigate to.
As for getting back to TOC, go to any one of the spreadsheets, in the developer tab, record a macro. Give the macro a shortcut, such as CTRL + w. Then, start recording the macro. From your active sheet, go to sheet 1 (the TOC). After this, stop recording. Now, whichever sheet you're on, you simply press ctrl + w to get back to toc. I tried this method, and it took only a couple seconds.
@@ExcelWithAnser Thank you so much for the prompt response, Anser. The macro answer is perfect, and I can create an icon for my quick-access toolbar. You rock!
Glad I could help!@@hankgrimes1806
Thank you for the video. Is it possible to get those hyperlinks in a form of a drop drown list?
Great point! I will look into this for a future video.
Would you not put a hyperlink on every sheet that would take you back to the TOC sheet?
Good point; you can do that. When you create a hyperlink to go back home on one spreadsheet, you can simply create a macro to generate the same hyperlink for all sheets.
Can you send the all formula in the beginning and all formula in this video from first to last because i cant see the exactly formula its blurd
Formula 1) =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)
Formula 2) = INDEX(ListWorksheets, A2)
(remember “listworksheets is the custom formula I made in name manager, and I named it this)
Formula 3) =HYPERLINK(CONCAT(“#”,B2,”!A1”), “Go to sheet”)
Thanks - really likes what this formula can do - however using Excel for Mac the "GET.Worksheet" formula does not exist - is there a work around to get this to work on a Mac also, thanks
Hi, do you have macros enabled?
@@ExcelWithAnser Thanks , that did the trick 👍
HI, the name of my sheets content - or _ (ex DNR-772 or 775_23) and the hyperlink function is not working, How can I improve it? I noticed the sheets without those caracters works fine
Hi, I apologize, I didn't get your question. Please explain.
I may be slow on the uptake, but when I copy and paste your REPLACE formula into my Create New Function pop-up in Excel, it tells me there's a problem with the formula. It seems to zero in on the "]" bit as the part it doesn't like. Thoughts? I hope I can get this thing to work because I have a pretty massive workbook. This would really make it easier to navigate! Thanks for any counsel.
I doubt this will work, but try replacing the square bracket with a round one. Otherwise, you can right-click the bottom left of your Excel sheet, on top of the sheet navigation arrows, this will open up a list of all sheets within your workbook, and you can navigate from there.
There's a navigation pane available. Go to customize your tab. Search under command.
Sir, thank u for ur video...its possible to incase any new sheet added on this sheet in automatically updated on first sheet is possible or not??
If the Worksheets have Space in the name, Can you please show me the changes needed in the Formula?
There is not a work around from my understanding. You should consider removing the spaces in the worksheet names and adding an underscore.
In the video description, you said you would copy the formula.
Hi Anser, I did not see the formula beginning with =Replace... Can you please put that formula here? Thanks very much!
Hi, =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)
@@ExcelWithAnser Thank you
The question is how to back in the content sheet in the same time and avoid clicking for each sheet to come back into the content
Very good question. I do not have an answer for you, but that is something I will consider for a future video. In the meantime, maybe you should look into recording a macro that could get you to the homepage using a keyboard shortcut.
Do you know is Excel 2019 is capable of these functions?
Thanks
Got the formulas to work. However it is saying that some of my references are invalid what could be the cause of this?
Thanks
Because you may not be using the same cell references as I did for my worksheet. You will have to adjust the formulas based on your specific worksheet. For example, I may have something in cell A1 of my sheet, and you may have the same thing in cell B7 of your worksheet.
Also, when I made the custom formula in Name Manager, I called in "ListWorksheets", and I used that name to link to a formula. If you didn't use the same name, then your formula will be different as well.
@@ExcelWithAnser Anser! I had to rename my sheets in the workbook to eliminate spaces! Should’ve thought about that before thank you for the reply tho and the video very useful!
Anser, when I try to set up the TOC with the name mgr. I keep getting #NAME# in the cell where the =replace function is placed and cannot get TOC to come up so I can copy down the sheet - solution??
Yes, I observed this error as well, and I'm unsure why it occurs. Please stay tuned for a future video, perhaps with a better alternative.
In the meantime, I'd recommend you right click the bottom left of your worksheet to navigate from there.
When I set up the Define Name in Name Mgr. with the 'list worksheet' and place the =replace in the right area, it does not appear to save in a list as your example does in the youtube instruction and I feel there is a glitch in this area when gong to set up TOC sheet - this is disappointing as I feel that this TOC set up is EXACTLY what am looking for having many spreadsheet files set up and this process is going to make organizing a little easier.
I agree, this is disappointing, especially because you are doing the exact same steps. Have you tried using another PC, perhaps there is a Windows/Mac or Excel Version discrepancy? @@jefffaulkner4492
Hi I am getting Name error in index formula, i am using 2019 version of excel, please help
Make sure you are using the formula correctly, referencing all cells properly, and that there are no spaces in any worksheet name
@@ExcelWithAnser Thanks for your reply.... I have removed all the spaces from the worksheet name but still I am getting #NAME? error in Index formula... please help... thank you
@@yashalinavalkar7354 Whatever you name your function in the Name Manager, that should be the name in your Index function as well. For example, when I created a new name in Name Manager, I called it "ListWorksheets". I then used this exact name in my Index formula.
Also, now that you have changed the names of all worksheets to remove spaces, try this process once again, from the beginning.
I did not get the formula pls send it to me and the video is not bright enough
Thanks for letting me know.
=hyperlink(concat(“#”,B2,”!A1”), ”Go to sheet”)
could you comment the formula in the refers to,
the replace formula thanks...
Kindly share the formula
Formula 1) =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)
Formula 2) = INDEX(ListWorksheets, A2)
(remember “listworksheets is the custom formula I made in name manager, and I named it this)
Formula 3) =HYPERLINK(CONCAT(“#”,B2,”!A1”), “Go to sheet”)
Sir jb hm mail me data send krte to ye hyperlink work nhi krta
=hyperlink(concat(“#”,B2,”!A1”), ”Go to sheet”)
Ap yeh formula try kijiye.
@@ExcelWithAnser ok thanks
@@ExcelWithAnser for some pages it's work and some are not
Error coming as " Reference isn't Valid)
How about putting the 1st formula in the comments.
=hyperlink(concat(“#”,B2,”!A1”), ”Go to sheet”)
You may need to alter the formula based on the positioning of your cells.
ERROR! The name you entered is invalid....
Hello, which function is giving you this error? It could be that your version of Excel doesn't recognize the function?
Am i blind? I dont see the formula for the "refer to"
=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)
@@ExcelWithAnserthank u!
I’m getting Name error
Make sure you are referencing the correct cells
AM NOT SEEING THE FORMULAR IN THE DESCRIPTION. ANY HELP
Hi,
=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)
A few other formulas were mentioned; please watch the video for those formulas, and tailor them to your spreadsheet accordingly.
I cant see any formula in description