MaKK
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
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
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_SheetFollowHyperl ink(ByVal Sh As Object, ByVal Target As Hyperlink)
If Len(Target.Address) = 0 Then
On Error Resume Next
Application.Goto Reference:=Application.Eva luate(Targ et.SubAddr ess)
' Position in center of window
ActiveWindow.ScrollRow = Application.Evaluate(Targe t.SubAddre ss).Row - Int(ActiveWindow.VisibleRa nge.Rows.C ount / 2 - 1)
ActiveWindow.ScrollColumn = Application.Evaluate(Targe t.SubAddre ss).Column - Int(ActiveWindow.VisibleRa nge.Column s.Count / 2 - 1)
' Position in top left corner
ActiveWindow.ScrollRow = Application.Evaluate(Targe t.SubAddre ss).Row
ActiveWindow.ScrollColumn = Application.Evaluate(Targe t.SubAddre ss).Column
' Position in row and one column down from the top left corner
ActiveWindow.ScrollRow = Application.Evaluate(Targe t.SubAddre ss).Row - 1
ActiveWindow.ScrollColumn = Application.Evaluate(Targe t.SubAddre ss).Column - 1
End If
End Sub
Kevin
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_SheetFollowHyperl
If Len(Target.Address) = 0 Then
On Error Resume Next
Application.Goto Reference:=Application.Eva
' Position in center of window
ActiveWindow.ScrollRow = Application.Evaluate(Targe
ActiveWindow.ScrollColumn = Application.Evaluate(Targe
' Position in top left corner
ActiveWindow.ScrollRow = Application.Evaluate(Targe
ActiveWindow.ScrollColumn = Application.Evaluate(Targe
' Position in row and one column down from the top left corner
ActiveWindow.ScrollRow = Application.Evaluate(Targe
ActiveWindow.ScrollColumn = Application.Evaluate(Targe
End If
End Sub
Kevin
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 :)
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
Kevin
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 :)
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
Kevin
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 :)
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
Kevin
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 :)
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
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
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 :)
$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 :)
ASKER
Er. What the.... something missing?
Max :)
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
Kevin
ASKER
Sorry, I'll get back to you asap tomorrow, must go.
Cheers for now, thanks,
Max :)
Cheers for now, thanks,
Max :)
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
$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
$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
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 :)
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 :)
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
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
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 :)
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
Kevin
ASKER
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
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
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
Max
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks muchly Kevin....
Cheers for now,
Max :)
Cheers for now,
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.Offs
AddIndex = True
End If
If AddIndex Then
Sheet1.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:="'" & Selection.Parent.Name & "'!" & SourceCell.Address, TextToDisplay:=UCase(Left(
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