INDEX() doesn't work with MID() and CELL() to list names of sheets

This macro will list all sheet names:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

^ Those are the instructions for Windows - I don't use Mac, so don't know if you have exactly the same (the 'Alt' key for example) - but hopefully that's enough to help. If not, please get back to us.

Originally Posted by Aardigspook

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

^ Those are the instructions for Windows - I don't use Mac, so don't know if you have exactly the same (the 'Alt' key for example) - but hopefully that's enough to help. If not, please get back to us.

Thank you very much for your help. I tried this, but, honestly, I need to learn a lot about VBAs before trying to use it.

EDIT: gave it another shot. I worked. But:

- it put the list in column 1 and cells starting from the second; not where I need it; so I decided to try and change the code;

when I put the 4 in Columns(1).Insert , that obviously inserted a new 4th column, which I didn't need; so I changed the code back;
then I decided to try to start the list not from i = 1, but from i = 33 (I need the list to start at D33), that did add a new column, but an empty one, with no list at all;

- I also miss a hyperlinks.

Is it possible to correct the macro in such way that these issues are addressed?
I have read that the approach I use with GET.WORKBOOK is not safe, so it would great to learn how to set a correct macro (or macros).
Thank you very much!

Another way using a UDF (User Defined Function)...

Create a range name (I called mine Sheetnanes
In the Refers To: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Originally Posted by FDibbins

Another way using a UDF (User Defined Function)...

Create a range name (I called mine Sheetnanes
In the Refers To: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Thank you very much! I have a post about the problem with creating Hyperlinks if I use the same approach you suggested.
Could you, please, explain to me how your hyperlink formula works, and also why the formula I found elsewhere doesn't? Does the first "#" stand for the name of the workbook?

I also have a question on why in both cases the INDEX formula is used twice within the hyperlink formula.

Here is the link to my previous post:
https://www.excelforum.com/excel-new...ml#post4683105

Here is the formula I asked about there:

Formula:

Please Login or Register  to view this content.

HYPERLINK has 2 parts - the 1st part creates the link, the 2nd part names it, so I used 2 INDEX's, firstly to create the link, and then to name the link based on what the link actually is.

As far as trouble-shooting that other formula - hard to say without seeing it in context

Originally Posted by FDibbins

HYPERLINK has 2 parts - the 1st part creates the link, the 2nd part names it, so I used 2 INDEX's, firstly to create the link, and then to name the link based on what the link actually is.

As far as trouble-shooting that other formula - hard to say without seeing it in context

I see now. I couldn't understand why we use INDEX twice. Now I understand that the second one creates a name. Thank you very much!
As to the another formula, I have provided a context, including the file, in my another thread, which I linked here. But! I have solved the problem thanks to your formula - I have found a few mistakes in that formula.

If by any chance you have time, could you, please, take a look at that 'old', but now corrected, formula. I still don't understand the role of one digit in it. When I use your formula, though a bit modified with the MID to get only the name of the sheet, I don't use that one digit, and it works fine. But, digit or not, both formulas work; so what does that digit do?

Formula:

Please Login or Register  to view this content.

Here is the digit I am talking about - it's the last one in the INDEX part:
INDEX(MID(excel_qns,FIND("]",excel_qns)+1,255),ROWS(A$1:A1),1)


And here is how I modified your formula - now it return only the name of the sheet.

Formula:

Please Login or Register  to view this content.

Does the "#" in (HYPERLINK("#" ... mean the name of the workbook?

Thank you very much!