Inserting different worksheet names in cells of one specific worksheet.

[lizot]

This is likely a very difficult one. I have read many posts regarding putting the name of a worksheet on a cell of the same worksheet, but nothing like I am looking for. Essentially, I am trying to build an index on 1 worksheet that would list the names of the other worksheet I want part of the index. The easiest way would be to type in all the names of the worksheets I want, but anyone can do this.

Has anyone attempted this before?

[FDibbins]

Hi Lizot and welcome to the forum

If you are trying to get a list of all sheet names onto 1 sheet, try this (I havnt used 2003 for a while, so im a little rusty as to where the commands are)...

1. go into names - define name
2. under "refers to" at the bottom of the window, enter this...
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
3. selct OK
4. In an empty column on the sheet you want the names, enter this, copied down...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

[lizot]

Haven't been able to make the function work. Also if it did work it would give me the list of all the sheets which is not what I am looking for. Only those I want. I expect the only solution will be to use the
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
and then from the sheet where I want the index built, to point to each one of the values captured. Can take some time, but it will work. There may be another way to build the index, but I just cannot think of it.

[FDibbins]

Perhaps if you uploaded a sample workbook, showing what you have, what you want, and how you arrived at that, it might be easier to help you?

Im thinking that indirect() might also be something you could try

[:) Sixthsense :)]

Or just copy paste the code provided in the below link by just adding a sheet called INDEX.

Refer the below link for details:-

http://www.ozgrid.com/VBA/sheet-index.htm

[lizot]

Thanks for both your inputs. I have learned a few more things in Excel. I ended up doing it the old fashion way because I did not have that many sheets to deal with and they would not have to be changed. Given that index I wanted was selective in nature would have required for me to ask way too many questions. Still this allowed me to look at other ways of doing indexing. The indirect function was also an eye openner. Thanks again.