Spreadsheet functions

[Paige Park]

I have a spreadsheet with our customers on it. The list is very long, so I
wanted to make some buttons at the top of the page that when you click on the
D, it takes you to the D's on the spreadsheet, etc. Is there any way to do
this in Microsoft Excel 2003?

[mrice]

You can do this with a macro like...

Sub Macro_D()
On error resume next
Columns(1).Find(What:="d*", After:=Cells(1,1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

This one searches for the first occurence of a cell beginning with a d (either case)

If you have 26 similar macros you can link them to buttons drawn with the forms toolbar.

[kassie]

You can do this. Create an alphabet, either by using buttons from the
toolbar, or simply using text boxes for each letter of the alphabet. To make
it easier to select, set line colour for each textbox to black, and use a
fill colour. The box now looks like a button of sorts. Define range names
where A, B etc begins, and name them something like AStart,BStart etc. Now
record a macro by clicking on Tools|Macro|Record macro. Press <Ctrl><G>,
type in eg BStart and press <Enter> Stop the macro recording. Press
<Alt><F11> to go to VBA. You will see your recorded macro. Rename it to Sub
BStart. You now have the code needed to write the rest of the macro's. Copy
the body - Between the () and End Sub - and copy it into the other macros.
Once you have done all of them, exit VBA, right click on each button, select
Assign Macro, and select the macro appropriate to that button. If you now
click on a button, say F, you will go to the start of F.

You may want to copy the A button to the start of each alphabet letter, to
enable you to quickly return to A, or create a Sart button, to take you back
to your alpha list.

Another way is to merely create the range names, and then press <Ctrl><G>,
tselect the range name and click on OK

"Paige Park" wrote:

> I have a spreadsheet with our customers on it. The list is very long, so I
> wanted to make some buttons at the top of the page that when you click on the
> D, it takes you to the D's on the spreadsheet, etc. Is there any way to do
> this in Microsoft Excel 2003?

[Toppers]

Here is a very simple example:

Cell D1 contains a Data Validation list of letters A to Z
Range C1 to C200 is your customer list

Use FORMS toolbox to put button on sheet and Assign Macro "FindAlpha" (code
below to be placed in a general module ... familar with Visual Basic Editor
VBE?)

Select letter in D1 and click button

If match is found it scrolls to first entry

If no match found, it doesn't scroll (you could add a message here if
required).

HTH

Sub FindAlpha()
Row = Application.Match(Range("D1"), Range("C1:C200"), 0)
If IsError(Row) Then Row = 1
ActiveWindow.ScrollRow = Row
End Sub

"Paige Park" wrote:

> I have a spreadsheet with our customers on it. The list is very long, so I
> wanted to make some buttons at the top of the page that when you click on the
> D, it takes you to the D's on the spreadsheet, etc. Is there any way to do
> this in Microsoft Excel 2003?

[Dave Peterson]

Another option is to use a shape from the Drawing toolbar bar and assign a
hyperlink (to a different location) to each shape.

Paige Park wrote:
>
> I have a spreadsheet with our customers on it. The list is very long, so I
> wanted to make some buttons at the top of the page that when you click on the
> D, it takes you to the D's on the spreadsheet, etc. Is there any way to do
> this in Microsoft Excel 2003?

--

Dave Peterson

[Tim M]

You could also just use the auto filter for this. Put the cursor in the
heading of the customer then go 'Data'....'Filter'....'Autofilter'. This
will put little drop down arrows that you can click. If you type a letter
key it will take you to the start of that letter in the drop down list.

"Paige Park" wrote:

> I have a spreadsheet with our customers on it. The list is very long, so I
> wanted to make some buttons at the top of the page that when you click on the
> D, it takes you to the D's on the spreadsheet, etc. Is there any way to do
> this in Microsoft Excel 2003?