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.

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 “]”.

Hi,

When you copy the formula from my site to your MS Excel workbook, straight quotes are converting to smart quotes (curly quotes) - which is why the error. After pasting the formula in MS Excel, please manually change the smart quotes (curly quotes) to straight quotes.

I still get the formula highlighting "]" as an error after changing the quotes. Any ideas?

Share more details. What are you trying to do? What formula have you written?

Having problems understanding how to use this formula to list all my sheets in the file?

Hi,

Please be specific. What exactly is the problem?

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

Hi,

Upload your file to SkyDrive and share the link of the uploaded file here.

Here's the link. http://sdrv.ms/10Zm2RO

I marked and described the areas to make it easier for you. Thanks for your help!!

Hi,

You may refer to my solution in this workbook

Hope this helps.

Ashish, you are a PRO man!!!! It worked wonders!!! Thanks for your help!!!

You are welcome. Thank you for your feedback.

Hi

Is it possible to get a copy of the workbook referred to in this link - I think it is the solution I am looking for!

Thank you

Julie

Hi,

The link to download the workbook is given in the Original post.

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

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

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

You are welcome.

Great Formulas.

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

The solution I have posted in the blog entry is a non macro solution. Though, I'm not sure if XLM macros will work in Named Ranges in Excel Starter.

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.

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.

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

Hi,

Upload the workbook to OneDrive and share the link of the workbook here.

Hi Ashish, I managed to crack it. It was the sheet names that had spaces. Thanks.

Dan

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

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.

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,

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

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

Hi,

Upload your workbook to OneDrive and share the link of the workbook with me.

Here's the OneDrive link. Thanks,

Vince Blair

https://onedrive.live.com/redir?resid=222B89262EA2D3B2!155&authkey=!AKsjlopuxpjr0Zc&ithint=file%2cxlsm

You've shared the workbook but have not described the question. What exactly do you want to do. Be very specific.

On the summary page, I want to list all the sheets in the workbook except for the summary sheet. I want the list of sheets to be hyperlinks that when clicked will open the corresponding sheet.

Hi,

You may refer to my solution in this workbook. Hope this helps.

Thank you Ashish. It works perfectly. Clearly I'm out of my depth here as the changes you made I wouldn't have been able to figure out on my own.

Thanks again!

You are welcome.

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.

Hi,

Have you replaced

Generate a list of all tab names without using VBA.xlsm

with your file name in my formula?

Sorry not sure what I did on Thursday when I tried this but apparently I had did something wrong. It works now. Last week I had renamed the file name but maybe I typoed or something not sure. Really appreciate the material though.

You are welcome.

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!

You are welcome.

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 !

You are most welcome. Thank you for your feedback.

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.

Hi,

Try this formula in cell D8

=INDIRECT(C8&"!A1")

Hope this helps.

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

You are welcome.

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?

Hi,

Upload your file to OneDrive and share the link of the uploaded file here.

Ashish, I have figured out the command buttons, but how do I auto-resize an image to a merged cell? I want to copy & paste or insert a picture into cell A4 in the file below and have not been able to find a macro or Sub that works in Excel 2007.

http://1drv.ms/1f5yYQm

Hi,

Your Comment has nothing to do with my Original Blog Title. Anyways, where are the images and would you be pasting the images manually in cell A4 and A16?

The link below has images that i would use for this, and yes I would be doing them manually by using the insert button or by copying image and pasting it into the cell.

https://www.google.com/search?q=webtron+750&espv=210&es_sm=93&source=lnms&tbm=isch&sa=X&ei=zY43U5KZCZC-sQSI0IKYAg&ved=0CAkQ_AUoAg&biw=1920&bih=955

Hi,

Please post your question in the MS Excel forums of Microsoft Community.

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?

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.

Hi Ashish,

Thanks for your response.

I checked the settings and the calculations are set to automatic.

Here is a link to the file on OneDrive as requested.

https://onedrive.live.com/redir?resid=52B87E68010D6BB4!1406&authkey=!AKte__DvXLoi0wM&ithint=file%2c.xlsm

Your help on this would be much appreciated.

Kind regards, Mel

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.

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.

Hi,

You are welcome. Does the problem persist on a Windows PC?

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.

Hi,

I do not use a Mac machine so I will not be able to help.

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!

Hi,

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

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!

Hi,

I cannot understand your question.

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?

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!

Thank you.

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

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

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.

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

Hi,

Could you please describe what you want to do. It is difficult to find an error in the formula if you do not describe your problem.

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?

Hi,

You may refer to my solution in this workbook.

Hope this helps.

Hello, I am using this solution but the array just shows one sheet with a name that meets the condition repeatedly, rather than the entire list, e.g.

Store1

Store1

Store1

Store1

Store1

Store1

Store1

Instead of:

Store1

Store2

Store3

Store4

Hi,

You have to press Ctrl+Shift+Enter - not just Enter. If it still does not help, then share the link from where I can download your file.

https://drive.google.com/file/d/1KXfbZ0IgkO_7BCft-tPvDJEh0EADXjrb/view?usp=sharing

Please see the "master" tab. I actually got it working afterwards without an array formula, but I thought I might like your version better.

=IFERROR(INDEX(Sheets,ROWS($A$1:$A4)),"Future Location")

Thanks for the help and your efforts on this site.

In which cell/range of that worksheet are you facing a problem. What results are you expecting on that cell/range?

Replying here as the thread has a limit to reply levels.

I would expect where it says "_Vancouver" repeatedly to say instead the names of all the cities: "_Vancouver; _Kelowna..."

This is on MASTER FRANCHISE, A7:A15.

Hi,

You have entered the array formula in the entire range A7:A15 at once. Select range A7:A15 and hit the delete key. Enter the array formula only in cell A7 and then copy down. It works fine for me.

You're right. Thanks.

You are welcome.

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

Hi,

Upload your file to OneDrive and share the link of the file here.

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

Hi,

Share the link from where I can download the workbook. Also, please colour the cells in yellow where you are facing a problem.

I have added a comment to the first cell highlighted in yellow explaining what i am trying to do.

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

I appreciciate your help with this.

Thanks,

Bobby

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.

You sir are awesome!

Thank You for the help!

Bobby

You are welcome. Thank you for your kind words.

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.

UPDATE: managed to figure it out, had some trouwbles with array formulas. Great help this post, thanks you again Ashish!

Max.

You are most welcome.

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

You are welcome.

Excellent! Gongrats and thanks to share this!

You are welcome.

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!

Hi,

Thank you. We will need to write a VBA code for that. I don't think this can be accomplished with a formula.

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?

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.

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

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.

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

Hi,

You may refer to my solution in this workbook. As and when you save the workbook with a different name and reopen the file, the hyperlink would work just fine.

Hope this helps.

Works Perfect, Thanks again Ashish!

You are welcome.

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.

Hi,

In cell D7, enter this formula and copy down

=INDIRECT("'"&C7&"'!AB53")

Hope this helps.

Thanks a ton buddy!!!

you saved a lot of my time 🙂

You are welcome.

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…

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.

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.

Hi,

Upload your workbook to OneDrive and share the link of the workbook here.

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

Hi,

There is no file at that link.

https://onedrive.live.com/?id=4B6244EFE53DB73F!108&cid=4b6244efe53db73f

If now not possible to access it pl. give me your email address so that i can send the file to you directly. i am very much interested in early solution to this problem pl.

Hi,

I do not answer questions on mail. The link you shared earlier is not working. You need to make the file public.

https://onedrive.live.com/redir?resid=4B6244EFE53DB73F%21108

Hi,

Try these formulas in the sheet4 worksheet

1. In cell E5 =INDEX(INDIRECT("'"&$A$3&"'!$C$1:$C$10"),Sheet4!$B$3,1)

2. In cell H5 =INDEX(INDIRECT("'"&$A$3&"'!$a$1:$a$10"),Sheet4!$B$3,1)

3. In cell E7 ="Paid to "&INDEX(INDIRECT("'"&$A$3&"'!$B$1:$B$10"),Sheet4!$B$3,1)&" US$"&INDEX(INDIRECT("'"&$A$3&"'!$D$1:$D$10"),Sheet4!$B$3,1)

Hope this helps.

Hi, Thanks a lot. I tried it but getting formula error. Not able to change as don't understand the logic. Pl.help asap.

Hi,

You may download my solution workbook from this link

Thanks a lot but still not able to convert figures into words. If that can be done, i will be highly obliged.

Hi,

The only solution to that is to write a VBA code. Please Google for the same.

Ok, no problem. Thanks a lot. I hope i will be able to manage it.

Hi, I have one serious problem. i want to check the sum of Debit&Credit columns which i am not able to establish. I want to show it as Payment Voucher, it figure is in Debit and Receipt Voucher if figure is in Credit. So i want to have formula of Credit minus Debit. So if figure is negative, it is Receipt and if figure is positive, it is Payment Voucher. But i am not able to establish that formula. I will be obliged if you can help me out. Thanks in advance.

Hi,

You may refer to my solution in this workbook. The narrative and numbers in Print Voucher will change depending upon whether the figure after subtraction is positive or negative.

Thank you very much. Great Help.

You are welcome.

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

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

Hi,

You are most welcome. You ay refer to my solution in this workbook.

Hope this helps.

This is awesome. Ist exactly what I needed. Thanks very much. :o)

gnarff

You are welcome.

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

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.

Hi,

That is strange. Upload the workbook to OneDrive and share the link of the workbook here.

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

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.

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!

You are welcome.

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

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.

Hi,

Thanks for a great formula made my life a lot easier

agron

You are welcome.

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

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!

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

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.

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.

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

Hi,

You need to copy down the first time.

Thank you, just thought I was missing something.

You are welcome.

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.

This can only be done with VBA coding.

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

You are welcome.

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

Hi,

Please post your question in the MS Excel forums of Microsoft Community.

Thanks Ashish.

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

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.

Amazing, thank you very much!

You are welcome.

Many thanks for the post, very helpful.

You are welcome.

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

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.

Hi Ashish,

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

Regards,

Alfred

You are welcome.

This is awesome!!

Thank you

You are welcome.

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

You are welcome. Glad to see that you could customize the formula to get your desired solution.

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

Hi,

I don't think that is possible. You may generate all sheet names in the same workbook and then simply link these cells to another workbook.

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

You are welcome.

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?

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.

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

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.

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

You are welcome.

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

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

Hi,

Try this

=INDIRECT("'"&A1&"'!J1")

A1 has sheet5 (returned by my formula).

Hope this helps.

Thanks. Using =INDIRECT(""&A1&"!J1") worked for me. I had to delete the extra "apostrophes".

Thanks for pointing me in the right direction.

K

You are welcome.

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

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

.....

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.

thanks Ashish. great formulas

You are welcome.

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?

Thank you. Can't say unless I see your workbook.

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

You are welcome.

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.

Hi,

When you click on the Formulas tab in the Ribbon, there is a button called name Manager under the Defined Names group.

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

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.

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

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.

This is absolutely Brilliant! Thank you!

You are welcome.

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

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.

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

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.

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!

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.

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.

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.

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.

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.

Hi,

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

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.

Hi,

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

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

Hi,

Sorry but i do not understand your question.

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

Glad i could help.

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

You are most welcome.

Brilliant. It worked perfectly!

Thank you.

YOU ARE MY HERO!

THANKS!

You are welcome. Thank you for your kind words.

Thank you its working

You are welcome.

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

Hi,

I really cannot say. Share the link from where I can download your file.

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.

You are welcome. No application crash for me when I right click.

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

Hi,

Thank you for your kind words. Share the link from where i can download your Excel file or a dummy file and clearly show me the result you are expecting.

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

You are welcome. In cell C4 of the Dashboard worksheet, enter this formula

=INDIRECT("'"&B4&"'!C6")

Hope this helps.