Link to home
Start Free TrialLog in
Avatar of MaKK
MaKKFlag for Australia

asked on

How to index Excel data

Hi!

Is it possible to create an index of cell data (in my case personal names)? And if so can it be done so that each entry is a link to click to go to the cell?

Would save a lot of manual labour....

With thanks,

Max

P.S. Am running Excel 2010
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

This is your lucky day, Max.

Add this macro to any general code module:

Public Sub CreateIndex()

    Dim Source As Range
    Dim Target As Range
    Dim SourceCell As Range
    Dim AddIndex As Boolean
   
    If Selection.Areas.Count <> 2 Then
        MsgBox "With the CTRL key held down, select the source data and then click in the cell where you want the index."
        Exit Sub
    End If
   
    Set Source = Selection.Areas(1)
    Set Target = Selection.Areas(2)
   
    Source.Sort Key1:=Source
    For Each SourceCell In Source
        AddIndex = False
        If SourceCell.Row = Source.Row Then
            AddIndex = True
        ElseIf UCase(Left(SourceCell, 1)) <> UCase(Left(SourceCell.Offset(-1), 1)) Then
            AddIndex = True
        End If
        If AddIndex Then
            Sheet1.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:="'" & Selection.Parent.Name & "'!" & SourceCell.Address, TextToDisplay:=UCase(Left(SourceCell, 1))
            Set Target = Target.Offset(1)
        End If
    Next SourceCell

End Sub

To create the index, hold down the CTRL key, select the source data and then click in the cell where you want the index, then run the macro.

Kevin
You will notice that when following hyperlinks, Excel does not position the target cell in a nice way. The instructions below provide a workaround.

When a hyperlink is followed to the target cell Excel scrolls the view just enough to bring the target cell into view. That means that of the view is above or to the left the target cell then the target cell will appear at the bottom or the right of the view. To always move the view such that the target cell is in the top left corner of the viewable area, add the code below to the ThisWorkbook VBA code module.

Note that this solution will not work if using the HYPERLINK function as that technique does not generate a "FollowHyperlink" event which is required for this solution.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    If Len(Target.Address) = 0 Then
        On Error Resume Next
        Application.Goto Reference:=Application.Evaluate(Target.SubAddress)
        ' Position in center of window
        ActiveWindow.ScrollRow = Application.Evaluate(Target.SubAddress).Row - Int(ActiveWindow.VisibleRange.Rows.Count / 2 - 1)
        ActiveWindow.ScrollColumn = Application.Evaluate(Target.SubAddress).Column - Int(ActiveWindow.VisibleRange.Columns.Count / 2 - 1)
        ' Position in top left corner
        ActiveWindow.ScrollRow = Application.Evaluate(Target.SubAddress).Row
        ActiveWindow.ScrollColumn = Application.Evaluate(Target.SubAddress).Column
        ' Position in row and one column down from the top left corner
        ActiveWindow.ScrollRow = Application.Evaluate(Target.SubAddress).Row - 1
        ActiveWindow.ScrollColumn = Application.Evaluate(Target.SubAddress).Column - 1
    End If
End Sub

Kevin
Avatar of MaKK

ASKER

Hey thanks Kevin!

Sorry mate it's not working for me.  
I hold down Ctrl, select say column B, click in one cell in column F, run the macro, let go of Ctrl.
Correct?

Also, I only want to index text strings (in cells) starting with a capital letter (e.g. Adam).

Max  :)
Don't select the entire column, just the cells containing the data you want to index. It will index all values in that selection.

Kevin
Avatar of MaKK

ASKER

Nothing yet...

This time -   I hold down Ctrl, select  a range of data, click in one cell in another column, run the macro, let go of Ctrl.  Correct?

Max :)
Yes, and then run the macro.

Kevin
Avatar of MaKK

ASKER

Sorry....

Hold down Ctrl,
Select  a range of data,
Select one cell in another column,
Let go of Ctrl.
Run macro.

Macro displays message box with instructions, click OK,

Nothing.... :(

Max :)
When you select the range in the first column you have to do so in one action. Each time you click the mouse you create another selection "region" and the macro is looking for only two regions. Are there values in the list that you do not want to index and are they not being selected?

Kevin
Avatar of MaKK

ASKER

No I selected a range of cells within a continuous list of (plain text) names.
Tried using click & drag, and also Ctrl.+ Shift + down arrow.

Back to you!


Max :)
Make your selection and run this macro:

Public Sub FigureOutWhatMaxIsDoing()
   Dim Area As Range
   For Each Area In Selection
      Debug.Print Area.Address
   Next Area
End Sub

Press ALT+F11, press CTRL+G, press CTRL+A, press CTRL+C, and paste the clipboard here.

Kevin
Avatar of MaKK

ASKER

$F$4
$F$5
$F$6
$F$7
$F$8
$F$9
$F$10
$F$11
$F$12
$F$13
$F$14
$F$15
$F$16
$F$17
$F$18
$F$19
$F$20
$F$21
$F$4
$F$5
$F$6
$F$7
$F$8
$F$9
$F$10
$F$11
$F$12
$F$13
$F$14
$F$15
$F$16
$F$17
$F$18
$F$19
$F$20
$F$21
$F$4
$F$5
$F$6
$F$7
$F$8
$F$9
$F$10
$F$11
$F$12
$F$13
$F$14
$F$15
$F$16
$F$17
$F$18
$F$19
$F$20
$F$21
$G$23

How about that!
Max :)
Avatar of MaKK

ASKER

Er. What the.... something missing?

Max :)
OK, you are clicking on each individual cell. You need to, while pressing the CTRL key down, click down on the first cell, drag to the last cell, and then release the mouse button.

Kevin
Avatar of MaKK

ASKER

Sorry, I'll get back to you asap tomorrow,  must go.

Cheers for now, thanks,

Max :)
Avatar of MaKK

ASKER

Made a selection again,

$C$38
$E$32
$E$33
$E$34
$E$35
$E$36
$E$37
$E$38
$E$39
$E$40
$E$41
$E$42
$E$43
$E$44
$E$45
$E$46
$E$47
$E$48
$E$49
$F$32

Ok?

Tried the CreateIndex macro again but still not working

Um,  here's an idea I had overnight that hopefully will clarify what my question envisioned, so to speak.  

MaKKro CreateDataIndex()

Create Index sheet
Go to first active cell
RetrieveData:
      If active cell contains text string beginning with uppercase letter (string may include hyphen and space)
      Retrieve string and cell number
      Convert to hyperlink
      Add to Index sheet
      End If
Goto next active cell

If text string exists in Index sheet
Go to next active cell

If text string not in Index sheet
Go to RetrieveData

Stop somehow ? at given range?
End MaKKro

Hope this  is helpful... ;)

Max

This shouldn't be that difficult. I can select a range of cells using one single mouse motion and then the destination cell. When I run the macro I see:

$A$1:$A$20
$C$1

Keep trying to select the first set of cells as follows: mouse down on the top cell and, without releasing the mouse button, drag the mouse to the end of the list, release the mouse button, then click on the destination cell. Do all of this while holding down the CTRL key.

Kevin
Avatar of MaKK

ASKER

Ok, I followed your instructions repeatedly but still no Index!  Nothing appears in the VBA Immediate window.
I notice when I hold down Ctrl. The currently selected cell  highlights. The I click and drag the list of names to index.  This might explain $C$38 appearing at the top of list in my previous comment.?

Max :)
Avatar of MaKK

ASKER

Hi Kevin,

Here's success!(?)   See attached file:

Did it this way:
Point mouse to first cell in list of names.
Select & hold on first cell.
Hold down Ctrl.
Drag to end of list of names.
Point mouse to & select first cell for index
Release Ctrl.
Run macro.

Max :)




MakeIndexTest.xlsx
Avatar of MaKK

ASKER

Hi Kevin,

 Sorry, as the names I need to index aren't in a continuous list I can't see how this method will suffice for my purposes.  Please consider my macro idea in the previous comment 11/01/10 02:58 PM, ID: 26280444.

Thanks,

Max :)

Can you upload a sample workbook that more accurately reflects your actual situation?

Kevin
Avatar of MaKK

ASKER

Thanks Kevin,

Here's a sample sheet.....

Max  :)


MKSampleSheet.xlsx
Unfortunately what you are looking doesn't make any sense. I thought you had a sorted list of names and you wanted to create an index into the first name in each letter of the alphabet.

What you have is a list of unsorted names. The best I can do is create a list of links to each name but it would still be half as long as the main list (one name per row instead of one name every other row.)

The bottom line is: what's the point?

Kevin
Avatar of MaKK

ASKER

  Well I'd like to have a separate sheet listing of all the people mentioned in the table(s) with a hyperlink that would take you to the first instance only of their names. The point is to provide a an easy way to navigate the workbook. Does this help?

Max
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MaKK

ASKER

Thanks muchly Kevin....

Cheers for now,

Max  :)