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

9 ай бұрын

Good point

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

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.

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

@ExcelWithAnser

7 ай бұрын

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

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

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

11 ай бұрын

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

11 ай бұрын

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

11 ай бұрын

Glad I could help!@@hankgrimes1806

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

8 ай бұрын

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.

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

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

@ExcelWithAnser

5 ай бұрын

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

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

@roccovergoglini7670

8 ай бұрын

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

7 ай бұрын

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

7 ай бұрын

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

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

9 ай бұрын

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.

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

9 ай бұрын

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

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

@ExcelWithAnser

2 ай бұрын

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.

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

8 ай бұрын

Hi, do you have macros enabled?

@kennethborges

8 ай бұрын

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

@ExcelWithAnser

5 ай бұрын

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.

@ExcelWithAnser

8 ай бұрын

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

8 ай бұрын

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

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

@ExcelWithAnser

8 ай бұрын

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.

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: kzread.info/dash/bejne/hpONxbqLYqvJoaw.html&lc=UgxhrS92A3lTUgOtK7N4AaABAg

@katherinep4210

8 ай бұрын

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

@c17nav

7 ай бұрын

@@katherinep4210 Try my method

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

@ExcelWithAnser

9 ай бұрын

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

@bhindaraghu

3 ай бұрын

@@ExcelWithAnser Thank you

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

@calcistaro3085

9 ай бұрын

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

9 ай бұрын

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

9 ай бұрын

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

Kindly share the formula

@ExcelWithAnser

8 ай бұрын

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

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

8 ай бұрын

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

8 ай бұрын

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

7 ай бұрын

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

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

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

@ExcelWithAnser

9 ай бұрын

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

@yashalinavalkar7354

9 ай бұрын

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

9 ай бұрын

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

How about putting the 1st formula in the comments.

@ExcelWithAnser

11 ай бұрын

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

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

2 ай бұрын

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

could you comment the formula in the refers to,

@GleasyJoyAtacador

4 ай бұрын

the replace formula thanks...

AM NOT SEEING THE FORMULAR IN THE DESCRIPTION. ANY HELP

@ExcelWithAnser

2 ай бұрын

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

2 ай бұрын

I cant see any formula in description

I’m getting Name error

@ExcelWithAnser

9 ай бұрын

Make sure you are referencing the correct cells

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

@ExcelWithAnser

9 ай бұрын

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

@mousmnwltr

9 ай бұрын

@@ExcelWithAnserthank u!

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

@ExcelWithAnser

9 ай бұрын

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