Generate a list of all tabs names without using VBA

{ 252 Comments }

By Ashish Mathur · · · Leave a comment

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.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • 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

  • 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

  • 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

    • 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

  • 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

  • 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

    • 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

      • 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

      • 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

        • 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

          • 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

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

          • 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

      • 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

  • 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

  • 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

  • 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

  • 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

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

    Reply

  • 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

  • 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

    • Hi,

      It looks like the calculation in the Workbook is set to Manual. Change it to Automatic and see if the problem gets resolved. If not, then upload your file to SkyDrive and share the link of the file here.

      Reply

        • Hi,

          I opened your file and could see all worksheets listed in range T2:T7 of “Working Data” worksheet.

          Please share the exact steps which are leading to your problem.

          Reply

          • That’s strange.
            Just each time that I save and close the spread sheet, when I reopen it, the T2:T7 range is blank.
            I can fix it by doing CTRL+SHFT+ENTER in the first formula again which then corrects all the others along with it.
            Could it be that I’m using it on a Mac?
            Not to worry though, it isn’t too big of an issue for me to do.
            Thanks for your help, I really love the formula.

          • Dear Ashish,

            We are creating a budget template for our company and is stuck with same issue in mac. In first go, when we open the sheet in mac, the formula is showing ‘SheetX’ instead of the actual sheet name.

  • 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

      • 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 – 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!

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

  • 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

  • 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

    • Hi,

      The GET.WORKBOOK XLM 4.0 macro function can fetch data from another workbook only as long as the other workbook is open. This can be accomplished with VBA only.

      Reply

  • 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: https://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

  • 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

  • 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

  • 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

        • Hi,

          Here are the two changed I made

          1. In the Named range box, rename then range to sheets
          2. In cell A3, enter this array formula (Ctrl+Shift+Enter)

          =HYPERLINK(“[Tooling_Projects.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),””))

          You may download the workbook from here.

          Hope this helps.

          Reply

  • 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

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

    Reply

  • 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

  • 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

    • You are welcome. Glad to help. You just have to ensure that ‘ exists in the INDIRECT() function. If you cannot work it out, then upload the workbook to OneDrive and share the link of the workbook here.

      Reply

  • 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

    • 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

  • 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

  • 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

  • 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

    • 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

  • 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

  • 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

  • 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

  • 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

    • 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

  • 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

  • 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

    • Hi,

      Thank you for your kind words. In any cell of the Summary sheet, enter this formula and copy rightwards

      =IFERROR(INDEX(MID(Sheets,FIND(“]”,Sheets)+1,255),COLUMN(A1),1),””)

      Hope this helps.

      Reply

  • 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

  • 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

  • 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

  • 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

    • 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

  • 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

  • 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

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

    Reply

  • 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

  • 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

    • Hi,

      That’s strange. I just downloaded the workbook from my website and after opening it, I click on om Enable editing. All cells in C7:C10 and D7:D10 showed the correct values.

      Reply

      • 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

        • 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

  • 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

  • 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

  • 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

    • You are welcome. My solution will work there as well. Please try to solve by yourself. If you face any problems, upload your workbook and share the download link here.

      Reply

  • 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

    • 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

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

    Reply

  • 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

  • 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

    • Hi,

      You are welcome. Let’s say you have all tab names in range A2:A10. In cell B2, enter this formula and copy down

      =INDIRECT(“‘”&A2&”‘!B4”)

      Hope this helps.

      Reply

  • 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

  • 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

  • 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

    • 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

  • 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

    • You are welcome. While this is a non VBA solution, the workbook still needs to be saved as a .xlsm because it makes use of XLM 4.0 macros. XLM 4.0 macros can only be used in named ranges.

      Reply

  • 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

    • Hi,

      Thank you for your message. As mentioned in my previous message, ensure that the calculation mode is set to automatic. Go to Formulas > Calculation Options > Automatic.

      Reply

  • 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

    • 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

      • 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

        • 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

  • 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

    • 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

        • 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

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

          • 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

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

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

  • 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

  • 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

  • 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

  • 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

  • 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

  • Hi Ashish,

    as above thanks for the tremendous time saving mesaures gained from your formulas and knowledge. it is a large help to a small business

    i would like to advance a little on the formulas used above

    essentially i have two questions

    1. i used the formula that didnt pull the dashboard into the list is there an easy way to not pull the template? (the use case is essentially users will duplicate the template tab and rename the tab each time we adda project

    2. is there a way to create a status update that pulls a value out of “C6” on each relevent tab each time the status is updated on its relevent project sheet to prevent needing to do it in two places each time a status changes

    Dummy file can be seen here
    https://www.dropbox.com/scl/fi/zjgugr12javxe0unl0omq/Project-Tracking-Report-dummy.xlsm?dl=0&rlkey=47rz7sdnp34jbn5arht2w0pjw

    Reply