Generate a list of all tabs names without using VBA

{247 Comments}

Posted by Ashish Mathur on

 in

Tagged: , , , , , , , , ,

Assume an MS Excel file has 4 worksheets - Sheet1, Sheet2, Sheet3 and Sheet4.  Insert a sheet before Sheet1 and name that tab as Summary.  On the Summary tab, one may want to generate a list of all sheet names from cell C7 onwards.  Furthermore, the sheet names so generated, should be dynamic for the following changes:

1. Sheets added
2. Sheets deleted
3. Sheets renamed
4. Sheets repositioned

While this can be accomplished by using VBA, you may refer to my formula based solution here.

To generate a list of all Excel files in a specific folder, you may refer to the following post.

RSS 247 Comments…

 Share your views
  1. Philip B January 25, 2013 at 7:33 am

    This would be a very useful function. I have followed the instructions exactly, however it is returning an error, highlighting this part of the formula “]”.

    Reply →
  2. Rafael Aviles June 3, 2013 at 11:38 pm

    Hi, I am going to use your formula which is excelent by the way. I am using it in combination with the one you have that automatically adds values from the same cell on diferent tabs =SUMPRODUCT(SUMIF(INDIRECT($AO$2:$AO$5&"!A2:A251"),AF3,INDIRECT($AO$2:$AO$5&"!B2:B251"))) . The problem I am having is that even though the tab list is being generated dynamically by this formula, I have to update the cell range manually, for example $AO$2:$AO$5 to $AO$2:$AO$6. If I make this range as big as I need it, lets say $AO$2:$AO$250, it gives me a #REF! error because tabs 6 thru 250 don't exist yet. Can you please help me??

    Reply →
  3. Mike September 20, 2013 at 8:39 pm

    Great info Ashish!

    You have really helped me solve a problem I have been trying to solve for a while. I was wondering if you could help take it one step further. If you don't need the background information you can skip to the last paragraph where I get around to the question.

    First a little background information. I have been attempting to "mine data" out of a colleagues poorly designed report spreadsheets. I have a set of approximately 50 cost reports that he created for different projects that I am now managing. Everyday there is work done on a project he adds a sheet to that projects report. Unfortunately, he is not consistent with his sheet naming structure. Fortunately, the table I am trying to "mine data" from is in a consistent enough location on each sheet that I can perform a VLOOKUP to pull the information I need from it as long as I can get a list of the sheet names. That is where you have helped me out immensely.

    I now want to take it one step further. I have developed a workbook that I use to pull the information from these reports. I use a slight modification of your formula in the Name Manager to pull this information into my workbook.
    =TRANSPOSE(GET.WORKBOOK(1,"Project1.xlsx"))&T(NOW())
    =TRANSPOSE(GET.WORKBOOK(1,"Project2.xlsx"))&T(NOW())
    ...
    =TRANSPOSE(GET.WORKBOOK(1,"Project50.xlsx"))&T(NOW())
    (the file names are actually the project names)

    I have made a table that I want to import into the Name Manager with the names in Column A and the "Refers to" formulas in Column B. Unfortunately, when highlighting the cells and using the "Create from Selection" feature of Excel 2010 it puts the Column B cell address in the "Refers to" field, not the actual formula. Is there another way to get these formulas into the "Refers to" field without typing or coping and pasting them individually?

    Thanks,

    Mike

    Reply →
    • Ashish Mathur September 21, 2013 at 6:18 am

      Hi,

      You are most welcome. I am glad that you found my solution useful.

      I am assuming that you objective is now to list down all tab names from the 50 (or more) workbooks stored in a certain folder. Is my understanding correct?

      Even if we can figure out a way to en-masse take all formulas to the Name Manager, the problem would be that this formula

      =IFERROR(INDEX(MID(sheets,FIND("]",sheets)+1,255),ROW(A1),1),"")

      will only work as long as the respective workbooks are open. If any workbook is closed, then the formula above will return a blank

      Since you will obviously not have 50 workbooks open at all times, I doubt if our effort to takes all formulas to the Name Manager will yield anything useful.

      Furthermore, even if we can figure out a way to retain the sheet names from closed workbooks, the INDIRECT() function which you may be using in your VLOOKUP() to pull data will definitely not work when the respective workbooks are closed.

      See if the following links on my website can be of any help

      1. Generate a list of all Excel files from a specific folder without using VBA
      2. Extract data from multiple cells of closed Excel files

      Reply →
  4. Brooke October 23, 2013 at 6:04 pm

    Just wanted to say...I've been looking for this solution for 2 days straight. Thanks so much!!

    Reply →
  5. AL November 11, 2013 at 4:22 pm

    Great Formulas.
    Is it possible to run the above formulas with out VBA? Since I'm using EXCEL starter which has very limited function.

    Reply →
  6. Jason December 18, 2013 at 9:48 pm

    Would it be possible to add a hyperlink to each of these automatically that would link to the page they correspond with?

    Also great job.

    Reply →
    • Ashish Mathur December 19, 2013 at 5:42 am

      Hi,

      Thank you. I am glad that you found my post helpful. Replace my formula in cell C7 with this one and copy down

      =HYPERLINK("[Generate a list of all tab names without using VBA.xlsm]"&IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),"")&"!A1",IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),""))

      Replace my formula in cell D7 with this array formula (Ctrl+Shift+Enter) and copy down

      =HYPERLINK("[Generate a list of all tab names without using VBA.xlsm]"&IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+1,255)MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+1,255))))),ROW(1:1)),1),"")&"!A1",IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+1,255)MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+1,255))))),ROW(1:1)),1),""))

      Hope this helps.

      Reply →
      • Dan January 20, 2015 at 10:42 pm

        Hi Ashish,

        Your hyperlink all tabs solution is great, however for a few tabs (appears to be random) the hyperlink does not work for me. Any ideas why not working for all sheets? Thks

        Reply →
      • Jacki Johnson February 26, 2015 at 6:13 am

        Hello Ashish!!
        Thank you so much for this post! I have incorporated the Hyperlink formula into my current worksheet and it's working great!! 🙂
        My problem is that my workbook is used weekly, I start with a blank workbook every Monday. Is there a way to have the hyperlink formula change automatically with the change of workbook name when the original file name is changed?

        Thanks so much!!! =)
        Jacki

        Reply →
        • Ashish Mathur February 26, 2015 at 12:40 pm

          Hi,

          Thank you for your kind words. I am glad that you found the post useful. If you start with a blank workbook every week, then you will have to rewrite all formulas in that new workbook. If you merely rename the existing workbook, then no change is required at all.

          Reply →
          • Lisa December 3, 2016 at 3:14 am

            Hi Ashish,

            Thank you so much for all of your help on this. I am clearly missing something. When I use the save as function and save the file with a new name, the formulas do not update with the new name and instead link to the old file. For example, if the working file is named Version1.xlsm and I resave it to Version2.xlsm, the formulas still link to Version1.xlsm. Is there a way to have the file name be dynamic in the formula?

            Thank you again for all of your help,

            Lisa

          • Ashish Mathur December 4, 2016 at 6:58 am

            Hi,

            You are most welcome. I do not face that problem. Here is what I did

            1. I renamed the workbook to Try.xlsm and saved it on my desktop
            2. I opened the file and renamed the first two worksheets to A and B
            3. The results in column C and D of worksheet A automatically updated

            Please retry.

          • Lisa December 5, 2016 at 12:34 am

            Hi Ashish,

            Thanks for looking at this so quickly. I too am able to resave the workbook with a different name, update the tab names, and have the results in column C and D of worksheet A automatically updated except for the file name to which they hyperlinks are linking. The old file name is still there, so when I click on the hyperlinks they try to open the old file. Are you not experiencing that?

            Thanks,

            Lisa

          • Ashish Mathur December 5, 2016 at 9:14 am

            Hi,

            If you have created the hyperlink via the hyperlink worksheet function, you will have to change the file name/path manually.

      • Vince Blair June 17, 2015 at 7:28 am

        Hi Ashish,
        I've also tried this hyperlink formula, and I get a "cannot open selected file" dialog box when I click the hyperlink formula. Any ideas would be great. Thanks

        Reply →
  7. Jason December 19, 2013 at 8:22 pm

    Definitely appreciate the help again. Only issue is this seems to generate a hyperlink to the file and tries to open a new file each time. I just need a hyperlink to the current workbook other tabs so I can use it to switch between tabs. Hopefully that makes sense.

    Reply →
  8. Debra March 5, 2014 at 3:18 am

    Thanks so much!!!! My 300+ tabbed workbook is so much easier to navigate once I created the Summary and then hyperlinked to the tabs. You made my day!

    Reply →
  9. Markus March 5, 2014 at 3:53 pm

    Ashish - The information you have provided here has been very helpful in developing my latest Excel app. Had assumed the old Excel functions were no longer callable in the recent releases of Excel (like GET. WORKBOOK). Man, I did such a lot of work with them a long time ago! Good to be able to pull one of these out of the hat and pop it into a defined name to quickly solve a problem. Many thanks - I mean it !

    Reply →
  10. Alex March 20, 2014 at 8:12 am

    Ashish, this worked great, but now I am trying to create formulas based on the list of tab names. So, where C8 = "Sheet1", I want to evaluate C8 as part of my formula and return the contents of cell A1 on Sheet1. I have tried =evaluate("C8!A1") and =exceleval("C8!A1"), and neither of these formulas work.

    Reply →
  11. Alex March 21, 2014 at 5:14 am

    Thank you, Ashish! That worked perfectly. I never saw that formula on any other other searches. Thank you!

    Reply →
  12. Alex March 23, 2014 at 2:06 am

    Ashish i have now created a Table of Contents with Command buttons that go to each sheet as well as a command button from each sheet to return to the table of contents. However when i add a sheet or change or delete a sheet i need the command buttons to change as well how would i make them do that?

    Reply →
  13. Mel April 3, 2014 at 4:48 am

    Hi Ashish,
    I have used your formula from the first post to get a list of my worksheet names.
    It worked beautifully and I have run some other formulas based on this list.
    However, when saving the sheet in .xlsx it warned me that it would loose the macro info etc etc. So I have saved it as a .xlsm which I noticed your version was.
    Now when I open it, the formula isn't working until I do CTRL+SHFT+ENTER in the first cell of this formula.
    Is there a way of getting this to work without having to do that each time I open the spread sheet?

    Reply →
  14. Bob April 29, 2014 at 12:15 am

    Ashish - I have my list of tabs and have incorporated that information into formulas needed to access data on all of the tabs. Is there a way to copy those formulas into cells rather than the value. I would like to copy/paste those formulas into many other cells in order to pull the data from surrounding cells. Thx!

    Reply →
    • Ashish Mathur April 29, 2014 at 4:27 am

      Hi,

      I am not clear with your request. Please rephrase and if possible, share the link of the uploaded working file.

      Reply →
      • Bob May 6, 2014 at 6:30 pm

        I use Texref to define flexible print areas by accessing spreadsheet parameters to define the print area: =DEFINE.NAME("Print_Area",TEXTREF(Monthlies!$JM$3,TRUE))
        The contents of Monthlies!$JM$3 would be something like:
        ="Monthlies!$CC$6:$DE$"&count($DE$6:$DE$260)
        The formula I build becomes my print area through the use of TextRef()

        I am curious as to whether or not there is a way to use spreadsheet parameters to build a formula that I would then somehow be able to use as that formula and not the text string as it was built.

        Thanks!

        Reply →
        • Ashish Mathur May 7, 2014 at 7:54 am

          Hi,

          I cannot understand your question.

          Reply →
          • Bob Walker May 7, 2014 at 6:12 pm

            Ashish - Thanks for your patience. I would like to build a formula that pulls data from each of Sheets. Using your algorithm, I now know the names of all tabs. I would like to have the tab name information automatically incorporated into a formula. I have no problem building the formula as a text string, but I do not know how to convert that text string into an actual formula to be processed in multiple cells. In my example above, I build the print area using the tab name and row/column calculations from the spreadsheet. That text string is still only a text string until I use "TextRef()" to actually recognize that string as a formula. TextRef is one of those old Excel 4.0 Macro Language functions that can only be used on a macro page. Thanks!

          • Ashish Mathur May 8, 2014 at 4:58 am

            Hi,

            I think you need to us the EVALUATE() XLM 4.0 function. In my solution available at this link, I have used the EVALUATE() function in the Named range. See if it helps.

  15. Rya April 30, 2014 at 2:57 am

    Hi, thanks so much! I've got it all working, including the hyperlink as you mention with:
    =HYPERLINK("[Generate a list of all tab names without using VBA.xlsm]"&IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),"")&"!A1",IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),""))

    BUT,

    It seems that doesn't work if the Sheet name has a space in it. Is there a way to make the hyperlinks work even with spaces in the name?

    Reply →
  16. Ryan April 30, 2014 at 3:04 am

    Hey I think I figured it out. I added &"'"& a couple of times in there to span the sheet name. Seems to work... Great article!

    Reply →
  17. Colin April 30, 2014 at 5:42 am

    Could you explain what you mean by span the sheet name?

    Reply →
  18. VK June 2, 2014 at 10:13 pm

    How can you get the tab names from a closed workbook in a separate master workbook

    Reply →
  19. Mo July 8, 2014 at 4:15 pm

    Hello, great help by the way from all the comments!

    With regards to an automated update of sheets, I've tried to mix hyperlinking with it, but have stumbled with a reference is not valid, this is the example of the code I used:
    =HYPERLINK("[STUDENT ATTENDANCE SAMPLE - Ashish.xlsm]"&IF(ISERROR(INDEX(WS,ROWS($A$1:$A1))),"",MID(INDEX(WS,ROWS($A$1:$A1)), FIND("]",INDEX(WS,ROWS($A$1:$A1)))+1,32))&T(NOW()))

    Where I added HYPERLINK("[STUDENT ATTENDANCE SAMPLE - Ashish.xlsm]"& at the start to the existing code.

    In addition, in one of your comments regarding the initial document you gave, hyperlinking the second column using
    =HYPERLINK("[Generate a list of all tab names without using VBA.xlsm]"&IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+1,255)MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+1,255))))),ROW(1:1)),1),"")&"!A1",IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+1,255)MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+1,255))))),ROW(1:1)),1),"")) - See more at: http://www.ashishmathur.com/generate-a-list-of-all-tabs-names-without-using-vba/#sthash.ySgg8L4I.dpuf

    .. needed a correction and with what excel suggested, it gave be a reference is not valid with a blank cell visually.

    Thank you for all your help, much appreciated,

    Mo

    Reply →
  20. R July 14, 2014 at 7:35 pm

    Interesting approach. I have a twist for you. Lets say I want to only add Sheets that begin with a word "Data"? or "Data*". Could that be done?

    Reply →
  21. Larry July 18, 2014 at 7:02 pm

    Ashish, Thanks in advance for your help.
    I do not understand how Ryan (posted on April 30, 2014 at 3:04am )was able to get the Hyperlink to work for sheet names that have one or more spaces in them.
    The hyperlink works for my sheeet names with out spaces but not the sheet names with spaces.
    Some of my sheet names have 5 or 7 spaces in the names.
    Thanks!
    Larry

    Reply →
  22. Bobby July 31, 2014 at 6:27 pm

    Hi Ashish,

    In my workbook the cells with the formula given return blank where it should be listing the tab names. I removed the iferror function and it returns error - #NAME?. Any thoughts on what my issue may be?

    Thank You!
    Bobby

    Reply →
  23. Max August 5, 2014 at 1:40 pm

    Dear Ashish,

    First of all - thank you for this great post. I'm just gonna need to ask for your help. I found to have the same issue as Bobby did: cells turned up blank. I followed your advice to Bobby, but now I can only get the first hyperlink to the first tab in the workbook to work. I can't seem to get the following tabs in the summary page.

    Can you give me some advice??

    Thank you for your help.

    Max.

    Reply →
  24. Jeanne August 8, 2014 at 5:53 am

    Thank you. This was just what I was looking for. Did need the reminder that the file needs to be macro-enabled (.xlsm).

    Reply →
  25. RODOLPHO F GODOY JR August 30, 2014 at 8:29 pm

    Excellent! Gongrats and thanks to share this!

    Reply →
  26. PraiseU September 3, 2014 at 12:24 pm

    Great article, Ashish. Thank you for this helpful formula!

    I was wondering if there was any way we can tweak the formula to show only the non-hidden worksheets?

    Thanks!

    Reply →
  27. Karen September 11, 2014 at 7:28 am

    Just. Thank you. I had a workbook with 90 sheets and they were driving me mad. Finding your solution was awesome and once Bobby chimed in with the spaces issue, I understood my problem. However, I couldn't get the spaces fix to work so I renamed the sheets. Glad there were only 90. Can you clarify what the formula looks like that allows the HYPERLINK formula to work with spaces in tab names?

    Reply →
  28. Tom September 14, 2014 at 11:26 am

    Please can someone help.

    I have managed to print out a list of worksheets that are in the same folder. (using name FL = FILES(""&Data!H9&"*.xls")&T(NOW()) )

    I now need to print out the name of each sheet in each worksheet.

    Any help would be much appreciated. Trying to stay away from VBA

    Reply →
    • Ashish Mathur September 18, 2014 at 5:14 am

      Hi,

      Try this

      1. Assume there is a folder named Test on the desktop
      2. In this folder there are two files - Book1.xlsx and Book2.xlsx
      3. In Book1.xlsx, there are two worksheets - Ashish and Mathur
      4. In Book2.xlsx, there are two worksheets - New Delhi and Noida
      5. Open Book1.xlsx and Book2.xlsx
      6. Open a third workbook and type Book1.xlsx in cell A2. Type Book2.xlsx in cell A3
      7. Go to Formulas > Name Manager > New and in the formula bar there, enter this formula

      =TRANSPOSE(GET.WORKBOOK(1,Summary!$A2))&T(NOW())

      8. In cell B2, enter this formula and copy down/right

      =IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),COLUMN()-1,1),"")

      Please note that this formula will work only as long as Book1.xlsx and Book2.xlsx are open.

      Hope this helps.

      Reply →
  29. Bobby October 6, 2014 at 6:36 pm

    I would like the cells in yellow too List/hyperlink all the sheets in this workbook.

    The issue I am having is that when I change the name of the work book (make a copy of it) the formula no longer functions - what the sheet is doing right now. I tried changing the name of the work book in the formula but this does not fix the issue.

    If you can resolve this issue let me know what is causing it.

    https://onedrive.live.com/redir?resid=A8B1FF53C39E5D56!112&authkey=!AJSjEDZyGYxdI7Q&ithint=file%2cxlsm

    Thank You,
    Bobby

    Reply →
  30. Bobby October 7, 2014 at 5:29 pm

    Works Perfect, Thanks again Ashish!

    Reply →
  31. Reuban October 27, 2014 at 3:59 pm

    Hi Ashish,
    Firstly thanks for this post which has helped to a great extent.
    Just required some more help. I have listed all the sheet names in a summary sheet as per your formula starting from cell C7. Now I want to reference values from each of those sheets (say cell AB53 on every sheet) to be besides the sheet names in cell D7 and below.
    I do not want to get into the complications of VBA. Could you provide me with some formula for this? Thanks in advance.

    Reply →
  32. Cfulge November 23, 2014 at 7:16 pm

    This is an impressive formula Ashish, and its not just the formula but the logic behind it (column D of your excel file). Although I opt to use your first set of formula (column D), I'm very much interested on how I can use "SMALL". I mean, yes, I know what it does now (after seeing your formula), but sometimes these fx isn't that much valuable if used alone, but along with other formula, it does make your life simpler and the worksheet dynamic. There’s just one point I didn’t quite get and I hope you can enlighten me... I'm trying to input the formula "=TRANSPOSE(GET.WORKBOOK(1))&T(NOW())" in excel cells but its resulting to error msg. My experience in using name range was that it can link to prepopulated tables. This is a new one for me, and I want to know the extent of formula and values you can just create in name range. I need to understand what this particular formula yields, so I can understand the whole formula, 'coz its ultimately where it picks up. Thanks in advance… 

    Reply →
    • Ashish Mathur November 24, 2014 at 4:44 am

      Hi,

      Thank you. That formula can only be used in Named ranges (not in cells). GET.WORKBOOK is an XLM 4.0 function. XLM 4.0 predates VBA. Since XLM 4.0 has been dropped now, the only way to use them is via Named ranges. GET.WORKBOOK returns the names of all sheets in the workbook, as a horizontal array of text values.

      Reply →
  33. Dilip December 19, 2014 at 9:49 pm

    Hi, i am novice in VBA and have simple problem.
    I have 4 sheets, sheet1, sheet2, sheet3 & sheet4.
    Sheet4 is Voucher Format to be printed.
    So i want to fill up data from Sheet1 through Sheet3
    So on top of Sheet4 i want to specify Sheet# & Row# , data of which to be printed.

    So my question is, how i can get those data on sheet4.
    Thanks in advance.

    Reply →
  34. dilip December 22, 2014 at 12:18 am

    https://onedrive.live.com/edit.aspx?resid=4B6244EFE53DB73F!109&id=documents&wd=cpe&app=Excel

    Reply →
  35. dilip December 26, 2014 at 12:20 pm

    Ok No problem. I have made the file public. Pl. reply asap. Thanks in advance.

    Reply →
  36. gnarff January 26, 2015 at 9:24 pm

    Hi,

    this is a real nice trick and a great help for me. I got rid of a lot of VBA code. Thanks for that.

    But sure, I got an additional question: How can I write a formula that runs through Sheet1, Sheet2, Sheet3 and gives me back a flat list of all items in A1:A10 in every sheet. I want to end up with a list like:

    Sheetname; Content
    Sheet1; Value of Sheet1!A1
    Sheet1; Value of Sheet1!A2
    ..
    Sheet1; Value of Sheet1!A10
    Sheet2; Value of Sheet2!A1
    Sheet2; Value of Sheet2!A2
    ..
    Sheet2; Value of Sheet2!A10
    Sheet3; Value of Sheet3!A1
    Sheet3; Value of Sheet3!A2
    ..
    Sheet3; Value of Sheet3!A10

    Is this possible?

    Thanks
    gnarff

    Reply →
  37. generate a list of all the Tab names February 17, 2015 at 9:14 pm

    […] document.write(''); This any good? Generate a list of all tabs names without using VBA […]

    Reply →
  38. blaua March 14, 2015 at 3:11 am

    Thank you for the formulas, they work great.

    Actually, I should say they worked great until today. I added your dynamic tab name formula and range in the Name Manager to several workbooks a few weeks ago and everything worked great. Today when I opened the workbooks, the formula in the sheet doesn't return any tab names. nothing was changed in the formula or name manager. The file was saved as a .xls type, and I even tried saving as a .xlsx and .xlsm type with no luck.

    Reply →
  39. Luqmaan Toure March 17, 2015 at 2:10 am

    Generate a list of all tabs names without using VBA - With Links

    Hi Ashish,
    Can you please supply the code to generate a list of tab names that includes spaces? I have read all the previous comments and Ryan (April 30, 2014 at 3:04 am) seems to have solved the issue, but I'm not a great coder so would appreciate the update – with the Links element also.

    Regards
    Luqmaan Toure

    Reply →
    • Ashish Mathur March 17, 2015 at 5:58 am

      Hi,

      My original solution works even when the tab name has spaces. Download the workbook from my original blog article and change the sheet names to give spaces. The names on the Summary sheet will reflect that change.

      If this still doe snot help, then upload your workbook to OneDrive and share the link of the workbook here.

      Reply →
  40. SoftBatch April 10, 2015 at 6:36 pm

    Great solution! It is incredibly powerful. I have a workbook with 30+ sheets and this formula returns the name of all of them! Great job and thank you!

    Reply →
  41. Petr April 26, 2015 at 2:26 pm

    Dear Ashish,
    you are really great. Could you also help me please with my problem. Your formula works well if I need a list of all sheets in a column, but I would need them in a row. Thank you in advance. Petr

    Reply →
  42. agron May 19, 2015 at 5:02 pm

    Hi,

    Thanks for a great formula made my life a lot easier

    agron

    Reply →
  43. David July 8, 2015 at 8:37 am

    Hi Ashish,
    Awesome post! I've reviewed all the comments and have used your examples to produce a list of hyperlinked (and non hyperlinked) sheet names for my workbook.
    However I am struggling to then use these sheet/tab name results in another formula such as a SUMIFS. I can see you have used INDIRECT on a single cell range to return a cell value, but I'm trying to return a SUMIFS that is dynamic and that will hopefully utilise the sheet name returned by your formula.

    As an example a sheet name is 2014_V3 and so one of my SUMIFS would be
    =SUMIFS('2014_V3'!AA4:AA103,'2014_V3'!E4:E103,"Feature1",'2014_V3'!G4:G103,"Feature2")

    Is it possible to use the result from your formula for the sheet name '2014_V3' in the above formula?

    Any ideas or a statement to say its not possible with this type of Range & Criteria formula would greatly assist.

    Many thanks for your time and assistance!
    Dave

    Reply →
  44. David July 8, 2015 at 8:49 am

    Ashish,

    I had one more look at INDIRECT and solved my own question straight after posting this question - I had been using INDIRECT outside of the SUMIFS formula, it needed to be embedded; (B31 has my sheet name result)

    =SUMIFS(INDIRECT(B31&"!AA4:AA103"),'2014_V3'!E4:E103,"Feature1",'2014_V3'!G4:G103,"Feature2")

    Hope this helps someone else too!

    Reply →
  45. David July 8, 2015 at 8:52 am

    Obviously the '2014_V3' needs replacing with the INDIRECT for each stage of the SUMIFS, I have only shown the initial Sum Range, but the Criteria Ranges would also need updating.
    Dave

    Reply →
  46. Prem Singh July 30, 2015 at 9:25 am

    as per your post Generate a list of all tabs names without using VBA
    is it possible that the same will be delivered if need all sheet tab names in a different worksheet.

    Reply →
    • Ashish Mathur August 1, 2015 at 5:49 am

      Hi,

      Yes it is. But the worksheet names will appear in the destination workbook only as long as the source workbook is open. If the source workbook is closed, then the worksheet names will yield errors instead. The best workaround to this problem is to generate all worksheet names in the source workbook itself (as described in my original Blog article) and then just refer to the these cells in the destination workbook.

      Hope this helps.

      Reply →
  47. Doug August 6, 2015 at 3:48 am

    Thank you for this formula.

    I entered the formulas in Name Manager and cells C7/C8 as instructed, but the remaining sheetnames do not populate. If I copy down the formula it will populate but it is not automatic as I expected. Also, if I create a new sheet that sheetname is not populated into the list UNTIL I change its location in the tab order.

    Is there a way to have the names dynamically/automatically generate or do I need to copy down the first time?

    Thanks again

    Reply →
  48. Rachel R August 8, 2015 at 7:01 am

    Hi, is there any way to reverse this formula? Such as if I have a list and want to create a tab for each separate list item along with the information associated with that list item? Thanks.

    Reply →
  49. LRZ August 24, 2015 at 3:52 pm

    Thank you so much, Ashish. This was an incredible time saver and your instructions were clear and easy to follow.

    Reply →
  50. Abhishek September 29, 2015 at 11:13 am

    Hi Ashish,

    Trust you are doing well!

    I need small help in automation. I am trying to filter my summary sheet, based on certain parameters. It also has Sheet names of all the worksheets in the workbook. After filtering the summary sheet, I need to take PDF print of only those worksheets which are in visible cells. Please suggest how can I do it.

    Btw, I am an NGO professional working for primary education in backward areas of western India.

    Regards,
    Abhishek

    Reply →
  51. Barry October 16, 2015 at 3:07 am

    Hi Ashish,
    I'm looking again at your xlsm file from your link. This all used to work just fine, but now it shows blank cells at c7:c10 and D7:D10. The formulas seem intact, but nothing is displayed! When the sheet is first loaded I can see the cell contents. At this point Excel shows a message "Protected view..." and a prompt to "enable editing". As soon as I click to enable editing the cells go blank!
    This does NOT happen on another computer running the same version of Excel.
    Regards, Barry

    Reply →
  52. Lowendo November 26, 2015 at 10:06 pm

    Amazing, thank you very much!

    Reply →
  53. Vikash January 11, 2016 at 8:26 am

    Many thanks for the post, very helpful.

    Reply →
    • Ashish Mathur January 11, 2016 at 8:54 am

      You are welcome.

      Reply →
      • Alfred February 12, 2016 at 11:31 am

        Hi Ashish,
        Good day! Appreciate all your help to us. Just want to ask if I want to skip the first three sheets of my workbook what adjustment should we place on your formula?

        =IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+2,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+2,255)MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+2,255),ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+2,255))))),ROW(1:1)),1),"")

        Reply →
        • Ashish Mathur February 13, 2016 at 10:47 am

          Hi,

          You are welcome. Try this array formula (Ctrl+Shift+Enter)

          =IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+1,255)<>MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+1,255))))),ROW(4:4)),1),"")

          Notice the portion in Bold.

          Hope this helps.

          Reply →
  54. Alfred February 15, 2016 at 10:05 am

    Hi Ashish,

    Thank you for the swift reply. You're my new idol now. Hehe

    Regards,
    Alfred

    Reply →
  55. Odette March 16, 2016 at 3:10 pm

    This is awesome!!
    Thank you

    Reply →
  56. Karol April 6, 2016 at 2:25 pm

    Hi, thank you for very nice article, it helped me a lot.

    I was trying to put sheet names [excluding the first one] into columns and ended up with this formula:
    =IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),COLUMN(A1)+1,1),"")
    where I've changed the ROW(A1) to COLUMN(A1) and added +1 to exclude the first sheet from the list, the other solution could be
    =IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),COLUMN(B1),1),"")

    Reply →
  57. Prem April 13, 2016 at 11:24 am

    Hello sir can we use your all sheet tab names formula in different workbooks is it possible.

    Reply →
  58. Peter May 25, 2016 at 3:40 pm

    Ashish,
    Thanks for this - I used your formula version to create a name which refers to a dynamic array of sheet names with which I can test each sheet via an array formula which only returns sheets with a problem (it checks a validity check flag) which lets me identify where problems are happening. I had previously done it via a UDF which worked but was temperamental; the array formula is much slicker and seems to be fool proof

    Reply →
  59. Navish June 6, 2016 at 10:27 pm

    Hi Ashish
    Thanks a lot for the formula. Incredibly helpful.

    I wanted to ask if the formula can be tweaked such that sheet names that contain more than one word separated by spaces can also be hyperlinked?

    Reply →
  60. Rod July 4, 2016 at 6:13 pm

    Hello Ashish,
    Firstly thank you for this formula, it's really very good.

    But I'm also struggling combining the Hyperlink option where tab names contain spaces. Always get a "Reference not valid" error message.

    I have uploaded file file to onedrive, perhaps you could assist me what I need to do to fix it. I'm also using Excel 2007 which is limited in the amount of levels of nesting you are allowed to use.

    https://1drv.ms/x/s!Aq_4tSPZ6ZnIjBKhRvaBdcjzO253

    Reply →
    • Ashish Mathur July 7, 2016 at 10:02 am

      Hi,

      You are welcome. I saved the workbook as a Macro enabled workbook. This formula in cell C1 of the Summary worksheet works fine for me

      =HYPERLINK("[TEMP C1485-10-20.xlsm]"&INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1)&"!A1",IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),""))

      However, when I copy this down, I get the error when I click on the hyperlink. I am sure it has something to do with the '. I tried but could not identify my error. Please go through the Comments in this Blog to solve the problem yourself.

      Reply →
  61. Joe Maffei July 26, 2016 at 9:12 pm

    This worked instantly for me. I just pasted in your sheet to the front of my other 35 sheets. Awesome. Thanks! Joe

    Reply →
  62. Consolidate data from several sheets to a summary sheet August 4, 2016 at 6:04 pm

    […] btw: You can automatically have a list of all worksheets in a workbook. See here. […]

    Reply →
  63. Kenneth Paterson August 10, 2016 at 11:43 pm

    Hi Ashish,

    I just used this formula in my file and it worked beautifully. Thank you.

    I would like to now populate data from the other sheets using the tab names. I was hoping some how to use your formula as the reference text to the page I want to retrieve the data from. Something like =Sheet5!J1 where "Sheet 5" is generated by your formula, and J1 is the data I want to retrieve.

    Anyway, I can't seem to make it work. I'm pretty new to this. Any help would be appreciated.

    Ken

    Reply →
  64. Can you dynamically change a link's destination? August 20, 2016 at 9:17 am

    […] document.write(''); Here is a link to get you going in the right direction: Generate a list of all tabs names without using VBA […]

    Reply →
  65. Austin October 11, 2016 at 12:07 am

    Thank you so much for this. It is very helpful.

    Is there anyway I could use something like this to reference a specific cell in each of the tabs?

    For Example:
    Tab 1 : b4 on tab 1
    Tab 2 : B4 on tab 2
    .....

    Reply →
  66. Jim November 11, 2016 at 4:56 pm

    thanks Ashish. great formulas

    Reply →
  67. Ashy December 19, 2016 at 10:54 pm

    Ashish,

    Wonderful work - all seems to be working well on the hyperlinked version, but when I changed your file name to my file name, I keep getting "Reference is not valid" when I try to click on the hyperlinks to my tabs. Any ideas?

    Reply →
  68. Bob January 4, 2017 at 11:13 pm

    Wow. You made this "old guy"'s day. Thank you!

    Reply →
  69. Kim February 20, 2017 at 3:10 am

    Hi Ashish,
    I thought I was pretty good with Excel, however I am confused as to how to get your formula working in my spreadsheet. I copied your 'Summary' page into my workbook. I don't understand your Step 1 at all. When I click on Formulas, I don't see anything that says 'Name Manager'. Could you provide more detail for step 1?
    Thanks.

    Reply →
  70. Nikolas March 6, 2017 at 10:57 pm

    Hey, this post is really useful even some years later!!

    I have a question thought... When I have only one book open, everything is ok. But when another book opens, the sheets names are replaced by the new book...
    Is there a way to "lock" the Defined name just to this workbook and not everyone?

    Thanks in advance,
    Nikolas

    Reply →
    • Ashish Mathur March 7, 2017 at 1:17 pm

      Hi,

      I do not face any such problem. When I opened the second workbook (I even saved it on my desktop), the names of worksheets returned by solution in workbook1 do not get replaced by the names of the worksheets of the second workbook.

      Please verify.

      Reply →
  71. Andy Miller March 24, 2017 at 6:15 pm

    Ashish,

    Thanks for this which is amazing and just what I wanted. My problem is that I was originally attracted to the solution because I can't have a Macro enabled Workbook. However, all of the examples I've downloaded on this thread are macro enabled and if I try to save the workbook as a standard version it will not allow the function in the Named Range, so technically this is not a macro free solution.

    Am I missing something?

    Andy

    Reply →
  72. Jeff April 19, 2017 at 6:43 pm

    This is absolutely Brilliant! Thank you!

    Reply →
  73. Alla June 21, 2017 at 1:22 pm

    Hello Ashish,

    Thank you very much for this post, and all your answers here. I am sorry for asking but, as a beginner in Excel, I will be grateful if you post a more detailed explanation of how your formulas work, what they mean, and how to use them.

    I downloaded the file, opened it with Enable macros option being on, and then saved the file under the name Text.xlsm. When I opened it, it displayed in cells C7 and C8 names of two sheets of the workbook I used **before** downloading your file; same names appeared in cells D7 and D8. Please, do me a great favor and explain how that works. I know it might be a bit tedious, but I will be truly grateful as I am just at the beginning of my learning curve.

    Also how safe it is to use macros? Don't they create a huge security risk?
    Thank you very much!
    Best,
    Alla

    Reply →
  74. Alla June 21, 2017 at 2:47 pm

    Hello Ashish,

    I am back with one more question. I have changed a code a bit to make sure that the name defined refers to the exact workbook. But I do have the following problem, which I consider an issue, though it might be a usual excel feature, which I don't know:

    I get the list of sheets' names, but when I add a new sheet, it doesn't automatically appear on the list; it appears only upon double clicking on the next cell that contains the formula.

    To be more precise:
    I added Sheet5, returned to the Summary sheet, and in cell C6 I still saw NO_MORE_SHEETS error message (I added it to see what's going on); the name of the sheet appeared only when I double clicked on C6.

    I did the same with another sheet, added Sheet6; same thing. But when I deleted that Sheet6, it automatically disappeared from the list.

    https://drive.google.com/open?id=0B-FEO3gzp3QZMjhVRW9jblA5T0U

    I will be grateful for your help!
    And tons of thanks to you for sharing this very helpful trick!
    Best,
    Alla

    Reply →
    • Ashish Mathur June 21, 2017 at 5:17 pm

      Hi,

      It works absolutely fine for me. I just downloaded your workbook and added a sheet6. In cell B7 of the Summary worksheet, I saw sheet6. Just ensure that your calculations are set to automatic.

      Reply →
      • Alla June 22, 2017 at 12:30 pm

        Ashish,
        Thank you very much for your answer. Yes, my Calculation options are set to Automatic, but still I have to double click on the formula each time I add a sheet. Might be some problem in my system (I use Mac OS 10.11.6 and Excel 14.7.5). I will need to look into this.

        One more question, if I may: could you, please, suggest how to expand the formula to create also hyperlinks for each sheet, i.e. to make each sheet name in the table of contents being a hyperlink (using a formula, and avoiding manually adding a hyperlink for each sheet).

        Thank you very much!

        Reply →
        • Ashish Mathur June 22, 2017 at 12:34 pm

          You are welcome. May be this solution does not work on a Mac - please check that. For your question stated in the second paragraph, please plod through my replies in the Comments section and I am sure you will find my solution somewhere there.

          Reply →
  75. Dave August 25, 2017 at 6:49 am

    Hey,
    I'm trying to use your formula to selectively list only those tabs/sheets that start with a specific set of letters and not any others.

    It seems that I need to filter the values being returned from "Sheets" formula but I'm unsure how to do that.

    Any help would be appreciated.

    Also, the sample that I downloaded works fine but when I copy the "Sheets excluding current sheet" to my own spreadsheet it only lists the 1st sheet and none of the others.

    Reply →
    • Dave August 25, 2017 at 7:24 am

      OK, figured out how to filter the list but still having problems with the formula that doesn't add the current page to the list... all i'm getting is the current page.

      Reply →
      • Ashish Mathur August 25, 2017 at 8:40 am

        Hi,

        What do you mean by "doesn't add the current page to the list... all i'm getting is the current page"? That is contradictory.

        Reply →
        • Dave August 25, 2017 at 8:54 am

          When I run your example .xlsm, both functions work correctly but when I copy them into a blank .xlsm the first funtion shows all pages in the list as it should but the second function only shows the first/current tab and none of the other ones.

          Reply →
          • Ashish Mathur August 26, 2017 at 6:24 am

            Hi,

            Ensure that you save the workbook as a .xlsm. Close it and then reopen it.

          • Dave August 26, 2017 at 10:19 am

            It is saved as .xlsm and have saved and closed/opened it a number of time... looking back at your original file... when I open it, the function that omits the page you are on, actually omits sheet1 and has the summary page in the list...
            Summary
            Sheet2
            Sheet3
            Sheet4
            Sheet5
            Sheet6

            and in fact should have Sheet1...Sheet6 and omit Summary.

          • Ashish Mathur August 28, 2017 at 9:36 am

            Hi,

            I just downloaded the workbook from my site and do not see any problem. The entries in range C7:C11 are Summary, sheet1, sheet2, sheet3, sheet4. The entries in range D7:D10 are sheet1, sheet2, sheet3, sheet4

          • Dave August 28, 2017 at 9:23 pm

            After doing some further testing...
            when I first open the the file, the entries in the range of D7:D10 are correct (sheet1...sheet4)
            if I then select one of the fields in D7:D10 click into the edit bar, don't change anything and tab back out then the results in fields D7:D10 change... D7 becomes "Summary" and the rest go blank as I tab out of the edit field.
            If I save the workbook at that point the fields remain messed up even upon reopening - If I don't save and reopen then it is correct. Clearly something is changing when I enter the edit bar and tab out even though I'm not typing anything.

          • Ashish Mathur August 29, 2017 at 5:29 am

            Hi,

            The formula in range D7:D10 are array formulas. After pressing F2, reconfirm the formula with Ctrl+Shift+Enter.

          • Dave August 29, 2017 at 5:50 am

            ok, that works but WOW.... you have to do that anytime that you enter the edit field, even if you don't change anything?

            ok, well thanks for the help, i guess I'll lock the fields so that can't happen.

  76. nadeem art December 7, 2017 at 12:14 pm

    There are 3 shifts, Every shift needs change duty after a Week (Sunday) of the month through 24:00 hrs. as given below detail.

    Date: Friday,01/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Saturday,02/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Sunday,03/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Monday,04/12/2017 (Duty Shift would be Changed)
    Shift:A 16:00 TO 24:00 hrs (Evening-duty)
    Shift:B 00:00 TO 08:00 hrs (Night-duty)
    Shift:C 08:00 TO 16:00 hrs (Morning-duty)

    Please help me that how to set formula in excel that will show me Shifts: A, B or C will perform their duties on given date in 24:00 hrs.

    May kindly please be helped me in this case. in advance I shall be very thankful for him/her.

    Thanks & Regards:
    Nadeem Sarwar

    Reply →
  77. Kristine December 13, 2017 at 2:54 am

    December 2017 and I just used a solution you posted in 2012 and it worked perfect! I used the solution on the link:
    "you may refer to my formula based solution here."

    Reply →
  78. Martien Arends December 27, 2017 at 2:03 pm

    Thank you very much for this advise. It proved to be very usefull.

    Reply →
  79. Dennis January 9, 2018 at 12:26 am

    Brilliant. It worked perfectly!

    Reply →
  80. AliciaCuriel January 26, 2018 at 9:03 pm

    YOU ARE MY HERO!
    THANKS!

    Reply →
  81. Raja July 4, 2018 at 1:05 pm

    Thank you its working

    Reply →
  82. Chloe Webb January 24, 2019 at 4:29 pm

    Hi, ive got a spreadsheet with multiple tabs. ive been using
    =IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),"")
    =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
    To get a list of the tabs, its been working fine for months. Now all of a sudden its stopped working and the formular wont pull though

    Reply →
  83. Alister May 8, 2019 at 10:42 am

    Great tip, thanks.
    The only thing people should be aware of is that (for me at least) it causes excel to crash if you insert a new worksheet in the workbook using the right-click menu. There is no crash if you insert a worksheet using the + button.

    Reply →
  84. Mike August 7, 2020 at 8:42 am

    Hi Ashish,

    I have to say, your formula has helped me so much and countless others (based on this thread), so thank you. You are truly an amazing individual.

    I hate to ask but I have been trying to figure out for a couple of weeks now and cannot figure it out.

    I am using the your formula to get all my tab names and have created hyperlinks in a separate column. I am now trying to figure out a way to omit 4 worksheets from my sheet list. My goal is to clean up my sheet list or "dashboard" but not having any errors or unnecessary data.

    So here is my question. How could I add to your formula to omit specifically named worksheets?

    =IFERROR(INDEX(MID(SheetList,FIND("]",SheetList)+1,100),ROW(B1),1),"")

    Thank you for your help.

    Mike

    Reply →

Leave a Comment Cancel reply

Your email address will not be published.

*

CAPTCHA

*