Create List and Hyperlinks Based Upon Cell Contents

Hello!

I was trying to create a table of contents that will automatically update itself with a macro and I have a good idea of how to implement it, but would appreciate some help with the macro.

Attached is a simple example of what I'm trying to do. I have a table of contents with the categories in a row and the contents of the categories beneath the category. In the spreadsheet, I have one of the contents linked to it's place in the other worksheet, but ideally, they would all be links to their place in the workbook.

Here is my general idea of what I want to happen:

1) The categories will automatically be generated by a worksheet name function, so I just put 1-15, use the function, and it will return the name of the 15 worksheets. (I can do this, so this step is complete.)

2) All of the contents of the categories (John, Jane, etc.) are located in defined names that follow this nomenclature....[worksheetname]3 (e.g. Purple3)....So I want the lists to be created by returning all cell contents of the defined name (there will be blank spaces, but I don't want to return those, just the cells that have something in them).

3) Create proper hyperlinks. I was thinking of some index/match combo to get the cell to hyperlink to the right place. If you have the worksheet (top of the list) and you have the name (John, Jane...), then just find "John" in "Purple3" and make the John link to that cell...if that makes sense.


It may seem confusing, but that's due to my poor communication. I don't think it's that hard. I'm just having trouble creating the macro to make the lists and then create hyperlinks for the things in the list. I'd be happy to expand on any of the above or clarify anything.

Thank you so much!

I think I can do #2 on my own as well. If someone could please help me with #3, I would very much appreciate it!

thanks!