Link Every Worksheet to a Master Sheet - Excel Organization Tips

[@SlickSlackHuman]

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...")

[@ExcelWithAnser]

Good point

[@viktorb.oskarsson8879]

Nice catch, but you are missing a comma following B2.

[@TasilaMwale]

Nice getting challenges going past the listing of sheets. How is he using the forular

[@inkmywh0L3b0dy]

Hi, I don't see the text here to copy and paste the formula. Can you share it please? I keep getting an error.

[@joseemomo6284]

Thank you for the video. Is it possible to get those hyperlinks in a form of a drop drown list?

[@ExcelWithAnser]

Great point! I will look into this for a future video.

[@kennethborges]

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

[@ExcelWithAnser]

Hi, do you have macros enabled?

[@kennethborges]

@@ExcelWithAnser Thanks , that did the trick 👍

[@hankgrimes1806]

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?

[@ExcelWithAnser]

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.

[@hankgrimes1806]

@@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!

[@ExcelWithAnser]

Glad I could help!@@hankgrimes1806

[@callumthorsen5474]

Would you not put a hyperlink on every sheet that would take you back to the TOC sheet?

[@ExcelWithAnser]

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.

[@mattjans]

@@ExcelWithAnser I used to do this process manually! Thanks for showing how to create the TOC. Do you have a video on how to create the hyperlink back to the TOC from each sheet? Still new to Excel macros. Thanks!

[@ndeshaafelalipuleni9390]

​@@ExcelWithAnser I didn't see your cursor where it pointed when you get the formula that you said it need to be copied.

[@viktorb.oskarsson8879]

Just ctrl click on the left arrow next to the sheets and you will go the far left and see the TOC sheet which you then just click on to go to.

[@calcistaro3085]

Do you know is Excel 2019 is capable of these functions? Thanks

[@calcistaro3085]

Got the formulas to work. However it is saying that some of my references are invalid what could be the cause of this? Thanks

[@ExcelWithAnser]

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.

[@calcistaro3085]

@@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!

[@fredyvanhalen]

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.

[@ExcelWithAnser]

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.

[@VanVu-k3k]

Hi Anser, I did not see the formula beginning with =Replace... Can you please put that formula here? Thanks very much!

[@ExcelWithAnser]

Hi, =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

[@bhindaraghu]

@@ExcelWithAnser Thank you

[@JUST....GAMING-WD]

Please suggest how to switch back to the Table of Contents.

[@viktorb.oskarsson8879]

Just ctrl click on the left arrow next to the sheets and you will go the far left and see the TOC sheet which you then just click on to go to.

[@JUST....GAMING-WD]

Thanks 👍

[@bradboardman8484]

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.

[@ExcelWithAnser]

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.

[@jesaheming]

There's a navigation pane available. Go to customize your tab. Search under command.

[@MohamedAttalla-fu1mj]

Would I be able to open hidden sheets with this formula?

[@ExcelWithAnser]

Hi Mohamed, I am not sure. Have you tried hiding a sheet and seeing if you can access it from the table of contents?

[@jefffaulkner4492]

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??

[@ExcelWithAnser]

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.

[@jefffaulkner4492]

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.

[@ExcelWithAnser]

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

[@juditalvarez2965]

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

[@ExcelWithAnser]

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”)

[@yashalinavalkar7354]

Hi I am getting Name error in index formula, i am using 2019 version of excel, please help

[@ExcelWithAnser]

Make sure you are using the formula correctly, referencing all cells properly, and that there are no spaces in any worksheet name

[@yashalinavalkar7354]

@@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

[@ExcelWithAnser]

@@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.

[@c17nav]

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.

[@punamsood3237]

your comment saved my time. Thank you

[@viktorb.oskarsson8879]

You can do what you mention with just a couple of sheets in workbook, then it's fine. But when you have 122 sheets in a workbook like I do this is awesome because when the table of contents sheet is finished I can sort the name of the sheets alphabetically and find what I need in a flash.

[@andriikulishov3371]

thank you, handy! what if you envelop with HYPERLINK the first column..? the idea is great! 👏👏

[@ExcelWithAnser]

That could be a viable alternative. I'd love to hear the results from you if you try it!

[@RS-vu5um]

If the Worksheets have Space in the name, Can you please show me the changes needed in the Formula?

[@ExcelWithAnser]

There is not a work around from my understanding. You should consider removing the spaces in the worksheet names and adding an underscore.

[@dolstoi4206]

thanks for the video but where is the formula in the descriptions?

[@ExcelWithAnser]

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”)

[@dineshmuthiah2127]

Kindly share the formula

[@ExcelWithAnser]

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”)

[@J6witdamix]

bro how do you have so many sheet examples open and you don't have an example for a work sheet with just a graph and no cells. Anybody know how to hyperlink this?

[@harekrushnanayak997]

Apku kacchu malum nehin one button then click all excel sheet how can open fully autometical.

[@mussa_junior116]

In the video description, you said you would copy the formula.

[@ExcelWithAnser]

Yes, I apologize. Here you go: 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”)

[@GleasyJoyAtacador]

could you comment the formula in the refers to,

[@GleasyJoyAtacador]

the replace formula thanks...

[@RabidMortal1]

Just right click the worksheet navigation arrow at the bottom. Ypu get a list of all the worksheets and can navigate directly to them!

[@roccov1972]

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.

[@XAE_A_Xii]

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.

[@c17nav]

@@XAE_A_XiiInstead of garbaging up the sheets with excess links, teach those recipients who are ignorant of Excel’s built-in capabilities.

[@fridayjohn8307]

I did not get the formula pls send it to me and the video is not bright enough

[@ExcelWithAnser]

Thanks for letting me know. =hyperlink(concat(“#”,B2,”!A1”), ”Go to sheet”)

[@reema8635]

Sir jb hm mail me data send krte to ye hyperlink work nhi krta

[@ExcelWithAnser]

=hyperlink(concat(“#”,B2,”!A1”), ”Go to sheet”) Ap yeh formula try kijiye.

[@reema8635]

@@ExcelWithAnser ok thanks

[@dinukaherath8102]

​@@ExcelWithAnser for some pages it's work and some are not Error coming as " Reference isn't Valid)

[@ExcelWithAnser]

@@dinukaherath8102 Make sure that the sheet names in your table of contents are the exact same as the actual sheet names.

[@djamelbek7506]

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

[@ExcelWithAnser]

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.

[@ranjeet2019]

I WANTED TO COPY THE FORMULA PL GIVE ME

[@ExcelWithAnser]

Hi, =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

[@belakulcsar]

ERROR! The name you entered is invalid....

[@ExcelWithAnser]

Hello, which function is giving you this error? It could be that your version of Excel doesn't recognize the function?

[@jeraldthalaiva85]

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??

[@mousmnwltr]

Am i blind? I dont see the formula for the "refer to"

[@ExcelWithAnser]

=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

[@mousmnwltr]

@@ExcelWithAnserthank u!

[@VijayBN-f1b]

I’m getting Name error

[@ExcelWithAnser]

Make sure you are referencing the correct cells

[@kamugishadeogratius7185]

AM NOT SEEING THE FORMULAR IN THE DESCRIPTION. ANY HELP

[@ExcelWithAnser]

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.

[@dinukaherath8102]

I cant see any formula in description

[@genehellickson3297]

How about putting the 1st formula in the comments.

[@ExcelWithAnser]

=hyperlink(concat(“#”,B2,”!A1”), ”Go to sheet”) You may need to alter the formula based on the positioning of your cells.

[@nasrnasr4537]

nice

[@ninas.2724]

I keep getting a Value error

[@ExcelWithAnser]

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: kzbin.info/www/bejne/ipC8lIuIZ62Wp6s&lc=UgxhrS92A3lTUgOtK7N4AaABAg

[@katherinep4210]

I cannot get your formula to work... I copied and pasted it and copied it from a picture and hand wrote it

[@c17nav]

@@katherinep4210 Try my method

[@fossilcrab]

All I get is #VALUE! On every line.

[@yulyamaia9194]

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

[@ExcelWithAnser]

Hi, I apologize, I didn't get your question. Please explain.