Lookup or index database type project?

In my mind this is probably a hard project. In most of yours though . . .

I want to create a list of hyperlinks in column A.
Row 1 will have a list of headers / categories
In columns B through Z I will simply place an "X" at the intersection of the link in column A and the category it would fit in across Row 1.

Now, on sheet 2, I would take the first category & it will list all the hyperlinks in sheet 1 column a that have an x in that category.

Sheet 3 would do the same thing with the next category, etc.

In the end I can add a hyperlink to the bottom of sheet 1, check off the categories it fits in, and it will populate the sheet for that category.

I have tried varying combinations of MATCH, LOOKUP, OFFSET, INDEX, etc. Of course, I am sure I did not do the right combination or typed it wrong, etc. (I know enough about Excel to know that I don't know much).

I tried IF statements & could get it to list the hyperlink, but the hyperlink would be text, not a hyperlink. Also, I could not get it to dynamically sort so that I don't have 50 blank lines between each link, etc.

The finished product will have approximately 400 hyperlinks and 40 - 50 categories. Also, the hyperlinks are to web sites.

If anyone thinks this could be done without too much hassle, please let me know. If it is too complicated, let me know that also & I will try something else.

My sincere thank you to anyone helping!

Post up a WORKING 3 sheet version of this with only a few categories and 5-10 rows to make it easy to manage as an example. Mockup the results on the three category sheets exactly as they should appear as a RESULT of the main sheet chart you're describing.

Showing us your complete goal in this manner lets us help most quickly and accurately.

For the links to travel to the secondary sheets too would require VBA. Your links are technically "formatting" applied to the database sheet cells and you cannot transfer the "format" of one cell to another via formula, only values.

Here's a macro I wrote for you. I added it to your workbook and connected it to a button for you. All you need to do is fill out the columns you want an put anything in the columns to mark them as active, "x" is fine, but the macro will work for anything you put in there.

Also, this macro will create the sheets for you on-the-fly. Just add a new column and that sheet will appear next time you run the macro. Also, the macro will put the sheets in the same order as the columns, so you can move columns around anytime you want based on priorities and the sheets will move to match.

Option Explicit

Sub UpdateLinkSheets()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      2/27/2011
'Summary:   Database entries updated to separate sheets for each category

Dim shName As String    'category sheet being updated
Dim LR As Long          'last row of data
Dim LC As Long          'last column with a category
Dim Col As Long         'current column (category) being used as source
                        'creates sheets as needed, puts sheets in correct order

Application.ScreenUpdating = False          'speed up macro

With Sheets("Database")     'all commands with a leading . affect this sheet
    LC = .Cells(.Columns.Count).End(xlToLeft).Column
    .AutoFilterMode = False
    .Rows(1).AutoFilter
    
    For Col = 2 To LC           'process each column from B onward one at a time
        shName = .Cells(1, Col).Text                           'get sheet name
        If Not Evaluate("ISREF('" & shName & "'!A1)") Then     'create sheet if needed
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = shName
        Else                                    'sheet exists, clear the current data
            Sheets(shName).Move After:=Sheets(Sheets.Count)
            Sheets(shName).UsedRange.Clear
        End If
        
        .Rows(1).AutoFilter Col, "*"            'filter for anything non-blank
        LR = .Cells(.Rows.Count, Col).End(xlUp).Row    'check for any entries
        If LR > 1 Then                          'if data, copy to category sheet
            Sheets(shName).Columns(1).ColumnWidth = 50
            .Range("A2:A" & LR).Copy Sheets(shName).Range("A2")
        Else                                    'no entries, make a note
            Sheets(shName).Range("A2") = "no links"
        End If
        
        .Rows(1).AutoFilter Col                 'show all rows again
        
    Next Col
    
    .AutoFilterMode = False
    .Activate       'return to Database sheet if any new sheets were created
End With

Application.ScreenUpdating = True
End Sub

Yes, you need to start a new thread.

Be sure to read through the Forum Rules so you can use and follow them to your benefit.

When you post your thread, include a link to this thread if you really think it relevant. Be sure to GO ADVANCED on your thread and use the paperclip icon to attach your sample workbook showing your data, and a mockup of your desired results.

Send me a Private Message with a invitation and a link to your new thread.