Excel Formula to List All Sheet Tab Names and include Hyperlinks

Make navigating Excel workbooks with lots of sheets easy with this clever formula that automatically updates as new sheets are added/moved/renamed. Download the Excel file here: www.myonlinetraininghub.com/d...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy

[@abdulhaseeb8027]

This is something I have never seen anyone do. You continue to amaze us by bringing these hidden features as tutorials.

[@MyOnlineTrainingHub]

Thanks so much, Abdul!

[@robrayborn1349]

I'm going to share this with all my Accounting friends! This is great! Thank you!

[@MyOnlineTrainingHub]

Great to hear it's of use to you, Rob! Thanks for sharing :-)

[@davegoodo3603]

Thanks Mynda, I liked it, smooth presentation and it reminds me that I need to spend more time in Excel doing things like this! Keep up the great work.

[@MyOnlineTrainingHub]

Cheers, Dave! Any excuse to spend more time in Excel, I say :-)

[@IvanCortinas_ES]

Excellent. Sparkly. Very solid work. Thanks Mynda!

[@MyOnlineTrainingHub]

Thank you kindly, Ivan!

[@garrydhillon8323]

You are awesome! looked for this all over the net. Well narrated and complied. Excellent Tutorial

[@MyOnlineTrainingHub]

Glad you enjoyed it, Garry!

[@prashantdeokule5989]

Your videos are "WOW".... Truly very informative and helpful. Thank you

[@MyOnlineTrainingHub]

Glad you think so!

[@KevD_]

Thanks for sharing this. I have worked with enormous spreadsheets in the past where this would have been useful. Always useful to have things like this stored away in case I might need the in the future.

[@MyOnlineTrainingHub]

So glad you'll be able to use it, Kevin!

[@70pjsmith]

Thank you so much Mynda, this was extremely helpful. I had previously found and used a MACRO that automatically creates a Table of Contents sheet within a large workbook, but it left some formatting issues that I haven't gotten around to tweaking within the VBA code. In light of that challenge, this was a great alternative.

[@MyOnlineTrainingHub]

Pleased to hear this is going to be useful to you :-)

[@countduckula9977]

Thanks Mynda, an extremely informative video!

[@MyOnlineTrainingHub]

Glad you liked it, Count Duckula :-)

[@mariaalcala5159]

Great video Mynda! Thanks a lot! It’s exactly what i need led right now!

[@MyOnlineTrainingHub]

Glad I could help, Maria!

[@arunrai2909]

Awesome as always!!! Wishing you a great year ahead😊

[@MyOnlineTrainingHub]

Thank you, and you! 😊

[@heikoheimrath7514]

Great video Mynda !!

[@MyOnlineTrainingHub]

Cheers, Heiko :-)

[@chrism9037]

Great video Mynda!

[@MyOnlineTrainingHub]

Cheers, Chris!

[@Divel11]

This is great, just what i needed for my recipe search :) have an awesome day!

[@MyOnlineTrainingHub]

Great to hear 😊

[@madhubhimanapelli6271]

Wow.... This was so helpful to jump in other sheet, it'll save much more time

[@MyOnlineTrainingHub]

Glad it was helpful, Madhu!

[@darrylmorgan]

Hi Mynda!Really Helpful Informative Tutorial..Thank You :)

[@MyOnlineTrainingHub]

Thanks so much, Darryl :-)

[@johnborg5419]

Amazing!!!!! Thanks Mynda. : )

[@MyOnlineTrainingHub]

Thanks so much, John! :-)

[@naveedyousaf1657]

Wonderfully Explained! Thanks for sharing.

[@MyOnlineTrainingHub]

Thanks so much, Naveed!

[@HimanshuTandon]

Wonderfully explained. Thank you so much.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@paulmfti]

So useful…thank you…such a great channel…cheers

[@MyOnlineTrainingHub]

Glad you think so!

[@hcandts]

Great functionality learned something new again, and it does beat macros for a quick look

[@MyOnlineTrainingHub]

Great to hear, Hans!

[@mahbubhossain1852]

It was truly helpful!

[@MyOnlineTrainingHub]

Great to hear!

[@grahamparker7729]

Nice one Mynda, haven’t seen this functionality before.

[@MyOnlineTrainingHub]

Great to hear you learnt something new, Graham :-)

[@katilivingood9720]

Super helpful and thank you for the download!

[@MyOnlineTrainingHub]

Great to hear 🙏

[@hazemali382]

always Great Mynda ♥

[@MyOnlineTrainingHub]

Cheers!

[@HajiBashir13]

Great video Mynda! Thank you

[@MyOnlineTrainingHub]

Thanks so much!

[@HajiBashir13]

@@MyOnlineTrainingHub pless send me this fills (Excel Formula to List All Sheet Tab Names and include Hyperlinks

[@MyOnlineTrainingHub]

The file link is in the video description!

[@sagar5238]

I really enjoyed watching your video. All these are really useful. Really thanks mam. :)

[@MyOnlineTrainingHub]

So pleased to hear that, Sagar!

[@shakiraasfoor7599]

Well Done Mynda

[@MyOnlineTrainingHub]

Thanks, Shakira!

[@frankconte467]

Thank you. Very helpful.

[@MyOnlineTrainingHub]

Glad to hear that, Frank! Thank you :-)

[@dhurandarbhatavdekar]

Thank you! You have been a saviour

[@MyOnlineTrainingHub]

Glad I could help!

[@dhurandarbhatavdekar]

@@MyOnlineTrainingHub there are some complex calculations that I intend to do. Would you help?

[@ivan2951]

Very informative!

[@MyOnlineTrainingHub]

Glad you think so!

[@JonathanExcels]

I didn't know about the # trick. Nice!

[@MyOnlineTrainingHub]

Glad it was worth your time to watch, Jonathan :-)

[@dashrathpanchal8393]

Thanks for sharing Mynda. I am frequently using this technique to navigate between lots of sheets in the workbook. But, Next complexity of this navigation is that; how to return at original cell where we clicked "Go to Sheet" because usually we will return back to "A1" cell on list of sheet which is by default. Here I am using below technique to get return at original cell 1. For e.g creating a name as "Ref"="C"&MATCH(REPLACE(CELL("filename"),1,FIND("]",CELL("filename"),1),""),listsheet!$C$3:$C$28,0)+2" (Above "listsheet" is main sheet of hypderlinks) 2. Create a hyperlink "Back" using above name "=HYPERLINK("#'listsheet'!"&Ref&"","Back") Please let me know how it looks and advise if any other way to return at the cell where we clicked "Go to sheet" without VBA Great learning from your tutorial videos !!

[@MyOnlineTrainingHub]

Hi Dashrath, The only way to navigate back to the previous cell is with VBA.

[@wayneedmondson1065]

Hi Mynda.. cool tricks using both old (4.0 macros) and new (dynamic arrays) features of Excel. I've solved this in the past a couple of ways with VBA with either a Sub and a button or event procedure on the worksheet or with a user defined function returning an array of the collection of sheets (all or just visible). Great stuff! Thanks for the tips, tricks and inspiration :)) Thumbs up!!

[@MyOnlineTrainingHub]

Cheers, Wayne! Yeah, the downside of this technique is that you can't exclude hidden sheets.

[@wayneedmondson1065]

@@MyOnlineTrainingHub Hi Mynda.. per the inspiration from your video, I built this solution: User defined function: Function ListSheets() Dim arr() As Variant Dim ws As Worksheet ReDim arr(1 To ThisWorkbook.Worksheets.Count, 0 To 0) Dim i As Integer i = 1 For Each ws In Worksheets arr(i, 0) = ws.Name i = i + 1 Next ws ListSheets = arr() End Function Use =ListSheets() on an Index sheet to spill the current list of worksheets vertically, say in cell B4 on Sheet1. In C4 enter: =HYPERLINK("#"&B4#&"!A1","Goto Sheet")&T(NOW()) to create the list of spilled hyperlinks. Format as needed. In the code window for Sheet1 enter: Private Sub Worksheet_Activate() Application.CalculateFull End Sub The above will give the functionality.. dynamic hyperlinks to any sheets you add or re-arrange in the workbook. Every time you activate the worksheet, the list will fully recalculate for any position changes or sheet additions/deletions. You could make it more bulletproof with a Workbook_Open event to be sure you have the correct updated list on first open. Also, you could modify to the For/Next loop to skip hidden sheets. Anyway, this was fun to create.. all inspired by your video.. so thanks and Thumbs up!!

[@MyOnlineTrainingHub]

Awesome! Thanks for sharing, Wayne.

[@wayneedmondson1065]

@@MyOnlineTrainingHub Thanks Mynda.. not to be overly verbose, but here is a more refined version of the UDF.. uses a collection to store only the visible worksheets and then uses it to populate a vertical array, as before. Always great inspiration and learning from your videos and blog. Thumbs up!! Function ListSheets() Dim coll As New Collection Dim ws As Worksheet For Each ws In Worksheets If ws.Visible = xlSheetVisible Then coll.Add ws.Name End If Next ws Dim arr As Variant Dim i As Integer ReDim arr(1 To coll.Count, 1 To 1) For i = 1 To UBound(arr, 1) arr(i, 1) = coll(i) Next i ListSheets = arr End Function

[@MyOnlineTrainingHub]

Nice! Not including hidden worksheets will definitely be a requirement for some users.

[@johbbro7931]

Awesome video Mynda, clearly presented and very useful! one note - Using cell reference "A1" for the hyperlink doesn't work for sheets that are charts.

[@MyOnlineTrainingHub]

Thanks, Joh :-) I never use chart sheets! I suppose if you want to use hyperlinks you'll need to put your chart on a regular sheet.

[@ljudevitgaj4855]

Thanks, excellent.

[@MyOnlineTrainingHub]

Glad you liked it :-)

[@ApteryxRex]

Thank you so much! I’ve been trying to make a right-hand dockable and/or floating form with dynamic list of sheet names with hyperlinks. So that sheet names are listed vertically ( wish this was an option in file settings) . Just to free up some vertical realestate and make it easier to see all sheet names. But this method could be just what I’m after.

[@MyOnlineTrainingHub]

So pleased this will be of use to you, Justin :-)

[@oyemnone8144]

Thank you 🎉

[@MyOnlineTrainingHub]

You’re welcome 😊

[@TrinhTuanAnh1981]

Great..Thank to all

[@MyOnlineTrainingHub]

My pleasure!

[@arturoguzman5230]

Awesome! 😎

[@MyOnlineTrainingHub]

Cheers, Arturo!

[@JaneSaranana]

Hi Mynda! This is really cool! Thanks so much for sharing these features. Is it necessary to keep the GET.WORKBOOK Function column in the sheet? I only want to show the sheet names with links in the worksheet.

[@MyOnlineTrainingHub]

Hi Jane, no, you don't need GET.WORKBOOK in the sheet, it's only necessary in the named formula.

[@chandrashekharbattula7656]

Thanks Mynda, this is helpful... There is one following scenario where I was not able to dynamically use hyperlink. i.e., if any of the sheet has table created from A1, then we get reference isn't valid error. any suggestion on how to overcome such cases ?

[@NemanjaJankovic2006]

Hi, Mynda. This is very helpful for me. I was wondering if it is possible to get some cell value from the sheets and add it on the index sheet next to a link cell? For example, I have guests lists for events, and I would like to have quick view if I still have free places for some of them on the index page. Thanks a lot.

[@MyOnlineTrainingHub]

Yes, you can do that. If you get stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@cindyc6703]

Hi Mynda, This is very wonderful explanation. I love it. You showed how to List All the Sheet Tab Names and navigator to the particular sheet just saying Sheet 2. How can I create a formula from Sheet 2 to List All the Sheet Names Tab? Thanks.

[@MyOnlineTrainingHub]

Hi Cindy, you can use the HYPERLINK function like this: =HYPERLINK("#Sheet2!A1", "Sheet2")

[@karenrabe1341]

Mynda: This video is very helpful; to me. I’ve been researching on-line for a good resource to make a table of contents with Hyperlinks, and this is definitely the source! One question for you. Is there a way to build this into the Excel template so it can be used on any open workbook instead of just the active workbook? Your assistance would be greatly appreciated.

[@MyOnlineTrainingHub]

Hi Karen, glad it's useful. The short answer is no. The file containing the list must be saved as a .xlsm type and contain the GET.WORKBOOK named formula. It cannot be accessed by an external file.

[@KM-co5mx]

This is very cool. Thank you! I have macros in my .xlsb files. Will this List All Sheet Tab Names method work for Excel Binary workbooks?

[@MyOnlineTrainingHub]

Yes, can't see why not.

[@rajiviyer9682]

Hi Mynda, This is quite innovative and just what I wanted! Thanks for the workaround for Excel 365 although it required an extra column. Curious to know how you discovered this and how were you creating something similar before discovering this method?

[@MyOnlineTrainingHub]

Glad it was helpful, Rajiv! Most of what I discover is the result of people asking me how you can do something. Then I have to find a way :-)

[@rajiviyer9682]

@@MyOnlineTrainingHub I have a question Mynda - Can we choose the worksheets that have to be hyperlinked using this method? For example I may have a mapping sheet or a working sheet that I would not like to appear in the list of Hyperlinked sheets.

[@MyOnlineTrainingHub]

No. You'd need to create the hyperlink manually if you want to omit some sheets from the list.

[@c17nav]

Totally superfluous. You don't need to create a new sheet to list the sheets in the workbook or devote space in other sheets for such a list. 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. Also, formatting with underlining and blue text is duplicated with the Hyperlink style. Using the style on the affected cells is more consistent so that modifications to the style (bold, italics, size, font, etc.) are automatically applied to the affected cells.

[@MyOnlineTrainingHub]

Great points. I still like to use an Index/Menu sheet as most users don't know about the hidden sheet list.

[@c17nav]

@@MyOnlineTrainingHub With all respect, Excel’s built-in method should be emphasized. Constructed hyperlinks in a spreadsheet are very useful to fire up external files and web addresses. I use such a sheet for shortcuts to different projects’ files. As for users that aren’t aware of the method, they should learn. After all, they or their company paid for the software. Not exploiting the software’s features is inexcusable. BTW, the method is much less “hidden” than a hyperlinked list on an added sheet or special area of any other sheet. The popup “Activate” box is available from anywhere.

[@KevinSmith-df5fz]

can I filter and only create links for sheets that start wiht @ for example?

[@iankr]

This is very useful, Mynda. Many thanks. I have a couple of questions, if you don't mind? 1. Does the # symbol as you've used it there always represent a shorthand for the workbook in formulae? I've not seen that before. 2. I prefer using the .xlsb (binary) format over .xlsm for macro-enabled files, as they are about 80% of the file size, which can make a difference for large files. Do you recommend using .xlsm? In fact, I always use .xlsb as the default for *all* of my files (rather than using .xlsx for non-macro-enabled) as they are always much smaller in size - the difference is even more marked than it is between .xlsb and .xlsm. Do you know of any issues with this approach of always using the binary format? Thank you.

[@MyOnlineTrainingHub]

Hi Ian, AFAIK the # symbol can only be used in the HYPERLINK function to resolve the workbook name. In other functions the # sign is the new dynamic array spill operator. Binary files do not have the macro security functionality of a .xlsm file, so that's the main downside. If you're using .xlsb for all your Excel files then you should also know that Power Query sometimes doesn't like working with this file type.

[@iankr]

Hi Mynda Thank you for this advice. So far, my use of Power Query (limited as it is) has been OK with binary files. But I will bear this in mind if I encounter any problems.

[@Katsumotoyeah]

Hi Mynda, thanks for sharing this video. Was wondering if there are any method where I can list the excel sheets that has formula referencing to particular sheet, as I have an excel that has many sheet tabs and some has formula referencing to another tab. I would like to remove those excel tabs that does not have any formula that references to that sheet.

[@MyOnlineTrainingHub]

You can use the Find tool to look for references in formulas "within the Workbook" for a specific sheet name and then List All.

[@SylvieBiard]

Dear Mynda, looks like it works even in an .XLSX file now.

[@MyOnlineTrainingHub]

hmmm, have you tried saving the file as .xlsx since you added the code?

[@DougHExcel]

what a neat trick (and without writing any vba!)

[@MyOnlineTrainingHub]

Glad you liked it, Doug :-)

[@vanitharosyva5783]

Hii Mam, All ur videos are of great help for me..!! I have a doubt.... can we get all tab names of a workbook in list box of user form based on a criteria?? It will b really helpful for me if u reply to this comment..

[@MyOnlineTrainingHub]

Thank you! I'm sure you can reference the list of sheet tab names in your user form. If you get stuck, please post your question and sample Excel file on our VBA forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@nazrinzahani9103]

This is great Mynda. Now how do I extract cell reference from each sheet? Example, say I have 100 sheets, how do i get cell reference A3 in all the 100 sheets into Sheet1 ?

[@MyOnlineTrainingHub]

Hi Nazrin, it's not clear if you want the sum of all sheets, or a list of the vales from each sheet. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@kadalisuhas1053]

Thank you it is really helpful. I am getting #Blocked while running this any help?

[@MyOnlineTrainingHub]

Please see this for reasons the #BLOCKED error occurs: support.microsoft.com/en-us/office/how-to-correct-a-blocked-error-13be117b-92e4-400a-a215-aa59d37d6e7c

[@alvaropena5540]

Great information. I have a workbook with 20 worksheets in to. I would like to have a main page with links to all the sheets, and hide the rest of the worksheets. How can i hyperlink and go to a hidden sheet?

[@MyOnlineTrainingHub]

Glad you can use this technique! You can't have a hyperlink unhide a hidden sheet. You'd need to use VBA for that. Might be better to just leave it unhidden.

[@alvaropena5540]

@@MyOnlineTrainingHub thank you. You're the bestest.

[@marionpaine7047]

This is very helpful but is there a way to make the index alphabetical without moving the sheets' positions? Thanks

[@MyOnlineTrainingHub]

If you have dynamic array functions, you can wrap TRANSPOSE in SORT e.g. =SORT(TRANSPOSE(...))

[@vitarathiel]

thank mynda. it's a usefull tutorial. i've tried in my excel 2013, unfortunately the sheetslist didn't auto change when i inserted/relocated/edited the worksheets. why ?

[@MyOnlineTrainingHub]

It shouldn't make any difference which version of Excel you use, so I suspect there's something not quite right in the set up. If you're still stuck, you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@vitarathiel]

​@@MyOnlineTrainingHubok mynda thank you for the answer. anyway i need to make the list of sheets - horizontally. how to generate in horizontal way ?

[@santhoshedamana]

Can you please make a video on POWER PIVOT in excel

[@MyOnlineTrainingHub]

Power Pivot is a huge topic, Santhosh! If you want to get up to speed with it quickly, I recommend taking a course: www.myonlinetraininghub.com/power-pivot-course

[@gstregistration4165]

Hello Minda, your explanation is flawless and initially i was able to get the desired output but once i saved the file as .xlsm and closing and reopening the list disappears. tha defined name and formula are properly saved but the return data shows as blank cell. Even on pressing F9 the data cell returns as blank. What can de done to resolve this error? thank you for the write up and all the help you provide. Regards RITESH BAFNA

[@MyOnlineTrainingHub]

Hi Ritesh, I replied to your duplicate question on our blog.

[@johnjohansson3756]

Awesome tutorial! One question though. Is there a way to ignore or exclude specific spreadsheets? For example, I'm working on a workbook where the first tab (spreadsheet) is a summary page and I don't want that listed in my list of spreadsheets names. In fact, I'm using your above illustrations to create the list on the summary tab which gathers information from the other spreadsheets. Thank you in advance.

[@MyOnlineTrainingHub]

Thanks, John! I haven't tried it, but if you have Microsoft 365 you could try using FILTER to exclude the Summary sheet. More on the FILTER function here: www.myonlinetraininghub.com/excel-filter-function

[@johnjohansson3756]

@@MyOnlineTrainingHub Thank you. I'm looking at it now, but it seems to only be filtering what should be shown, not what to be excluded. My other tabs each identify a different vehicle in our fleet (i.e. 2006 GMC Sierra, 2015 Chevrolet Silverado, 2014 Honda Odyssey, etc.). Thanks, again. I'll keep looking to see how I can use the FILTER function to filter out what I don't want shown (i.e. Summary) when the other tabs could be very varied in name. Oh, and yes I am using 365. :)

[@MyOnlineTrainingHub]

I'm thinking the formula will be a bit like this: =FILTER( your formula, your formula"Summary") but that could be wishful thinking :-)

[@johnjohansson3756]

@@MyOnlineTrainingHub Awesome! I got it to work! :) Here is my formula that worked, with your help, in case you want to use it somehow. =FILTER(TRANSPOSE(sheetnames),TRANSPOSE(sheetnames)"Summary 2021")

[@mousmnwltr]

Yea, I am having a similar issue too. i need a dynamic list of worksheets that excludes hidden worksheets. I could hide them , but then the list is no longer dynamic. This is the formula i've been using that doesn't exclude hidden worksheets. what am i missing? =IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(C$1:C2)&T(NOW())),"")

[@edgardomachado2704]

very food application Mynda...I have a question? I did a Excel test on line and I fail however in practical I am good am I missing something

[@MyOnlineTrainingHub]

Glad you enjoyed this tutorial, Edgardo! I couldn't comment on the reasons for your test result, sorry.

[@ronarmitage2205]

All worked great when adding the index, yet, my file is saved in .xlsm format and my Macros are enabled yet the formulas are still blocked. Thoughts?

[@MyOnlineTrainingHub]

Hi Ron, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@murbansk1]

​@@MyOnlineTrainingHub10:17

[@nimrashabbir7001]

Is the get.workbook function used in the video available in excel 2016?

[@MyOnlineTrainingHub]

Yes, it's available in all versions of Excel.

[@nimrashabbir7001]

@@MyOnlineTrainingHub Hey, Whenever I write Get.workbook function in my formula Like =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") I got the ( That Function isn't valid error) and once I click on it, I got a grey highlighted area around my get.workbook function. I would like to know how to resolve this error. Thanks for your reply Mynda.😊

[@shoppersdream]

Mynda... Did you cover this topic with Power Query also or no? We are not allowed any macros. Thanks

[@MyOnlineTrainingHub]

I supposed you could use Power Query to return a list of sheet names and then use the HYPERLINK function to create the links.

[@shoppersdream]

@@MyOnlineTrainingHubMyOnlineTrainingHub Thank you, Mynda! I was able to find another video that used Power Query. Thanks

[@TheDarthpsi]

Is there a way to skip over or exclude some tabs with specific names using this technique?

[@MyOnlineTrainingHub]

No, you'd have to write specific VBA code to ignore some sheets.

[@thomasw.857]

Thank you so much for putting up this video. I tried it out and it worked great. However, when my colleague open the same file and tried to use the same. They got the error message that showed #BLOCKED. I would be grateful if you could let me know why this happened.

[@MyOnlineTrainingHub]

Could be many reasons for the #BLOCKED! error. See here for troubleshooting: support.microsoft.com/en-au/office/how-to-correct-a-blocked-error-13be117b-92e4-400a-a215-aa59d37d6e7c

[@user-ix1sh6ig8m]

If there is space or "-" in the Sheet Name, it will give "Reference isn't valid", could you please advise how to fix it? otherwise this works perfectly fine. Thank you

[@MyOnlineTrainingHub]

Don't use spaces or - 😉

[@nimrashabbir7001]

Hi Mynda, how can we include hyperlinks to the sheet list created with the macro?

[@MyOnlineTrainingHub]

Not sure what you're referring to. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@nimrashabbir7001]

@@MyOnlineTrainingHub I would like to know how we can add hyperlinks to the sheet list generated by the macro? and thanks in advance.

[@senju2035]

Can this whole thing be done on excel online

[@MyOnlineTrainingHub]

No.

[@manishbhattarai8358]

How do I get the list to show up using excel 2007 please?

[@MyOnlineTrainingHub]

It should work in the same way in Excel 2007.

[@teoxengineer]

Mynda hi, I have not found this function (Get. Workbook) in Turkish Excel. Can you help me what the name of this function is in Turkish Excel Formula?

[@MyOnlineTrainingHub]

Hi Emre, I'm not sure if they're available in other languages as this is a very old function and a Macro function at that. Have you tried using it?

[@teoxengineer]

@@MyOnlineTrainingHub Hi Mynda, I have not used it but, it is so miracle function as I saw in your lecture. However, I couldn't find it my Office365 and use it my reports. I tried to find in excel help pages but nothing. It looks so handy and powerful in order to navigate my report pages...🤔 I want you to help me 🙏🏻

[@MyOnlineTrainingHub]

Hi Emre, you don't find this function by typing it in a cell. You have to define it in a name as you saw me do in the video. I recommend you try it and see if it works by following the steps in the video.

[@teoxengineer]

@@MyOnlineTrainingHub I'll try it again. Thank you Mynda👍

[@anhnhatnguyet4628]

How to export all sheet names in an open excel file, then return the results to Google Sheet?

[@earlthomas7464]

Do you have any one on one classes (I always run into issues with excel). It's guaranteed that I will run into a brick wall with excel. I quit taking live classes because my screen almost never looks like the instructor's screen. Please help me!!!

[@MyOnlineTrainingHub]

Hi Earl, I don't do one on one training, however I do offer support for all of my courses, so if you get stuck I'm here to help. Alternatively, if you get in touch via email (website at MyOnlineTrainingHub.com) I can put you in touch with someone who does one on one training. Mynda

[@earlthomas7464]

@@MyOnlineTrainingHub I will take a look at your website. Thank you.

[@jimtischler558]

Hello! I followed this tutorial about two months ago to build out a facility reference. I opened this today to distribute out to have the respective worksheets filled out but all of the links are invisible and not working! I can still see the formula in the formula bar, but I am not seeing the actual links! Is there anyway you could help to figure out what happened?

[@MyOnlineTrainingHub]

I suspect you didn't save the file as a .xlsm file, which is required for Macro functions.

[@jimtischler558]

@@MyOnlineTrainingHub No, it is definitely a .xlsm file. Like I said, This was working and I had some colleagues vet it when I created it. It has sat untouched for two months until I opened it on Friday

[@jimtischler558]

@@MyOnlineTrainingHub After doing some digging, In the security settings somehow the Macros were disabled so I enabled them as well as the 4.0 macros, save and reoped and wallah, it was working again! Why would Macro Settings be disabled in a .XLSM workbook, any ideas?

[@MyOnlineTrainingHub]

Macros get disabled because they can be a security risk. It could be a company wide setting. It usually happens when you open a file from an email or downloaded online.

[@murbansk1]

Hi .just tried following this tutorial ,and got stuck on the last bit hyperlink. I get reference is not valid mistake.what am i doing wrong?

[@MyOnlineTrainingHub]

Hard to say. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@chrissanchez352]

Hello, I currently work for a non-profit and I'm trying to do exactly this for the company I work for and I'm getting stuck almost immediately. When I type in the formula you've given I'm only getting the 1st sheet. Not a list of sheets. Any idea what I'm doing wrong?

[@MyOnlineTrainingHub]

Hard to say without seeing your file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@chrissanchez352]

@@MyOnlineTrainingHub Thank you so much! Your video is awesome! And after a while I was able to fix my issue. However, my second questions would be, is it possible to omit certain sheets from the list?

[@jingtingong6177]

Hi, why my transpose do not return to many row? it just return to one value?

[@MyOnlineTrainingHub]

No sure. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@madhubhimanapelli6271]

Hi Mam, Have a question What it result?? if we put =GET.WORKBOOK(2), =GET.WORKBOOK(3), and so on.... What are the uses of second argument in given function

[@MyOnlineTrainingHub]

Please download the list of 4.0 Macro Functions that contains that information here: www.myonlinetraininghub.com/excel-4-macro-functions

[@eddiep2899]

ha-HA!

[@ssi8189]

Mam, is it possible to extract csv file from zip file available at net in excel query, i am trying it but failed, is it possible in excel power query, please guide

[@MyOnlineTrainingHub]

You need to unzip them first.

[@ssi8189]

@@MyOnlineTrainingHub ok, thanks

[@nkowk]

👍👍👍👍👍😊😊😊

[@MyOnlineTrainingHub]

Thank you!

[@satyabratasethy286]

How to auto hide and open the sheets system which was hyperlinks.

[@MyOnlineTrainingHub]

'The sheets system'? Not sure what you mean by that. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@jamalhasanzakarneh9837]

how can do it using Microsoft Office 2016. Get workbook does notwork

[@MyOnlineTrainingHub]

Yes, it works in all versions of Excel. I suspect you didn't define a name. You can't just type the function into a cell.

[@jamalhasanzakarneh9837]

I tried again, you are totally right. It works when I defined a name. I wonder why it is not work directly I mean as fucntion. Thank you Mynda

[@leedsunitedfan8494]

I will give this a try. Cheers.

[@pamelagrishampalmer7799]

I followed the video and even copied/pasted the link and I get "Reference isn't valid" - =IFERROR(HYPERLINK("#'"&INDEX(SheetNames, ROW(A1))&"'!A1", INDEX(SheetNames,ROW(A1))),""). I have looked over this for the last two hours trying to figure out what I have done wrong. Please show me my error.

[@MyOnlineTrainingHub]

Hi Pamela, a 'reference isn't valid' error means you have a name that's not recognised. Most likely SheetNames or the names in that range.

[@pamelagrishampalmer7799]

@@MyOnlineTrainingHub Thanks - it's got to be ones of the sheet names from the tabs - I'll be taking a look.

[@captvo]

All I can say is .... "EXCEL"LENT !!!

[@MyOnlineTrainingHub]

Thanks so much!

[@lalitrawat7369]

I tried by didn't succeed.😔

[@MyOnlineTrainingHub]

I'd love to help you, Lalit. Please post your question on our Excel forum where you can include your Excel file and I can see where things went wrong: www.myonlinetraininghub.com/excel-forum

[@Deependra1991]

How old are you. Coz in 5yrs ago videos and now videos you look same. Just adding no to the ages and not growing old at all.😀😀

[@MyOnlineTrainingHub]

Thanks so much, Deependra :-)

[@Giu.Tanaka]

Mynda, thank you. Em português, consegui utilizar, colocando em gerenciador de nomes: =EXT.TEXTO(INFO.PASTA.TRABALHO(1);LOCALIZAR("]";INFO.PASTA.TRABALHO(1))+1;100)&T(AGORA())

[@MyOnlineTrainingHub]

Great to hear!

[@beatrizlopezsanchez4036]

When I enter the formula =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&t(NOW()) in Define Name appear a message that says that "When the fist caracter is an equal("2) or minus ("-·") sign, Excel thinks it's a formula". I think I put the formula correctly but it does nor work. Could you help me?

[@beatrizlopezsanchez4036]

I put that formula to Define the Hyperlink =REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW()) but now, when I do the formula Transpose the result is #BLOCKED! I do not understand what's the error

[@beatrizlopezsanchez4036]

It is solved now! Thanks a lot for this fantastic tutorial!!!

[@MyOnlineTrainingHub]

Great to hear. Sorry for the delayed reply. Glad you got it working.

[@TSZ0111]

the get.workbook function returned #N/A, why is that

[@MyOnlineTrainingHub]

Please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum