I have an Excel file with 9000 records and 15 fields sorted alphabetically. I
want to set up the letters of the alphabet at the top so that if I click a
letter it will scroll to the first record wih that letter. I have seen this
often on WEB pages. Can it be done in Excel? What is the name of this device?
If it is a program, where can I find it? I have Office 2003 Excel.

Thanks for your help.

Bob Barckley

You could put the alphabet (one character in each cell) at the top of your list.

The assign a hyperlink to that cell that goes to location that you want.

Select the cell
Insert|hyperlink
Place in this document
type in the cell address.

Alternatively, you could use the =hyperlink() formula:

This will go to A19 of the activesheet and display L in the cell.
=HYPERLINK("#a19","L")



BobB wrote:

I have an Excel file with 9000 records and 15 fields sorted alphabetically. I
want to set up the letters of the alphabet at the top so that if I click a
letter it will scroll to the first record wih that letter. I have seen this
often on WEB pages. Can it be done in Excel? What is the name of this device?
If it is a program, where can I find it? I have Office 2003 Excel.

Thanks for your help.

Bob Barckley


--

Dave Peterson

One more option if you want.

Put 26 little textboxes from the Drawing toolbar in row 1 and rightclick on each
and assign hyperlink to the cell you want to go to.

By using the textboxes, you can position them whereever you like on the
worksheet and not have to worry about column widths.

BobB wrote:

I have an Excel file with 9000 records and 15 fields sorted alphabetically. I
want to set up the letters of the alphabet at the top so that if I click a
letter it will scroll to the first record wih that letter. I have seen this
often on WEB pages. Can it be done in Excel? What is the name of this device?
If it is a program, where can I find it? I have Office 2003 Excel.

Thanks for your help.

Bob Barckley


--

Dave Peterson

Hi!

This uses VBA and need not take long to assemble.

Put 26 little rectangles from the drawing toolbar at the top of your
worksheet - so that in due course that part can be frozen there.
Easiest way I know is to make 1 to suit my taste on colour size font
etc and copy/paste the rest.

Be meticulous about their "names" which will come up in the Name Box as
Rectangle 1, Rectangle 2 etc. We are going to use those to match A - Z.

Somewhere out of the way (say, column Q) put A,B,....,Z in a column.
These will be the captions for the rectangles.

Right select the first rectangle and in the formula bar type =Q1 where
Q1 is where you stored "A": enter. The rectangle should now have an A
in it. Edit if you are not happy, but I suggest you leave that till
later when you can do them all at one go.

Paste this macro into a module (Insert Module)

Public Sub FindAlpha()

Dim CallerID As String
Dim N As Integer
Dim sCaption As String
Dim R As VbMsgBoxResult

CallerID = Application.Caller
N = Val(Right(CallerID, 2)) + 64
sCaption = Chr(N)

Dim i As Integer
i = 1
While i < 2000
If UCase(Left(Range("A" & i), 1)) = sCaption Then
Range("A" & i).Select
Exit Sub
End If
i = i + 1
Wend
R = MsgBox("No entries for the letter " & sCaption, vbOKOnly)

End Sub

I put the <2000 condition in as both safeguard (infinite loops) and
laziness (you could find the length of the column instead)

Finally, connect all of the 26 buttons to this macro. Shift-click on
all 26: then right-click on them and select Assign macro. Choose
FindAlpha from the list.

Have fun!

Alf

I think I came up with a fairly easy and slightly automated solution to this:

1)Create a new sheet in the workbook

2)In cells A1:A26, enter the letters A through Z.

3)Name the A1:A26 range "AlphaList".

4)Name cell B1 "LetterOpt".

5)Name cell B2 "LetterNew".

6)Name cell B3 "TargetCell"

7)Put this formula in cell B2: =INDEX(AlphaList,LetterOpt,1)

8)Put this formula in cell B3:
="A"&MIN(IF(Sheet1!$A$10:$A$500=NewLetter&"*",999 999,ROW(Sheet1!$A$10:$A$20)))
Note: Hold down [Ctrl]+[Shift] when you [Enter] to commit that array formula.

9)Next, on the data sheet:
-place a combo box control from the Forms toolbar up in Cell A.
-Name the control cboLetter.
-Set the Input Range to AlphaList.
-Set the Cell Link to LetterOpt.

10)Right click on the control and select Assign Macro and select a New macro.

11) IN the cboLetter_Change sub put this code:
On Error Resume Next
Sheet1.Range([TargetCell]).Select
If Err 0 Then
MsgBox "No Match"
End If

Note: replace the Sheet1 ref anywhere above with whatever your data sheet
name is.

I think that should do it.

Does that help?

Regards,
Ron

Hi Bob,

I think you will like this one better, you simply right-click
from anywhere and type in something like B or Barc
or T or Thompson something that starts from the beginning
of the cell in column A. If you want to make sure a "W" is found
include each letter of the alphabet as a cell value before those that
begin with that letter. No hyperlinks to maintain and can zero in
better if you know what name you want and there are 500 lastnames
that begin with "W".

MATCH Worksheet Function in a macro as a substitute for
hyperlinks, in a a name and address list. (#match)
http://www.mvps.org/dmcritchie/excel/event.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"BobB" wrote ...
I have an Excel file with 9000 records and 15 fields sorted alphabetically. I
want to set up the letters of the alphabet at the top so that if I click a
letter it will scroll to the first record wih that letter. I have seen this
often on WEB pages. Can it be done in Excel? What is the name of this device?
If it is a program, where can I find it? I have Office 2003 Excel.

Thanks for your help.

Bob Barckley