Hi,

I have a work sheet that lists produts in alphabetical order.

The top of my work sheet displays the alphabet and each letter has a cell.

Under this row the letter A is and it has 7 blank rows under it - to display
products beginning with the letter A and then the letter B is on the next row
and this has 7 blank rows underneath it and it will store products beginning
with B etc.

Can a function be set up to do the following. When I select a letter from
the alphabet on the top row say P, the page automatically goes to my list of
products beginning with P.

If possible I would prefer not to use macros.

Any help would be much appreciated.

Thanking you in advance,

Bill

Function cannot perform actions. Therefore, you are unable to select another
cell with a function. This would need to be done with a macro.
I would suggest you so a worksheet change event and have the end-user update
a cell with the value the want to see. It would look something like this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("a1") Then
Select Case Target.Value
Case "A"
Range("a7").Select
Case "B"
Range("a14").Select
Case Else
Range("A21").Select
End Select
End If
End Sub


"Bill" wrote:

Hi,

I have a work sheet that lists produts in alphabetical order.

The top of my work sheet displays the alphabet and each letter has a cell.

Under this row the letter A is and it has 7 blank rows under it - to display
products beginning with the letter A and then the letter B is on the next row
and this has 7 blank rows underneath it and it will store products beginning
with B etc.

Can a function be set up to do the following. When I select a letter from
the alphabet on the top row say P, the page automatically goes to my list of
products beginning with P.

If possible I would prefer not to use macros.

Any help would be much appreciated.

Thanking you in advance,

Bill


Hello Bill,

Function can not proform actions.

If, as I understand what you said, you have A in a cell by itself, B in a
cell by itself, etc, then right click sheet tabview codeinsert thischange
columns(1) to reference your column. Now when you type in B or b in cell a1
you will be taken to the row where the B rows start.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
Columns(1).Find(Target, lookat:=xlWhole).Select
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bill" wrote in message
...
Hi,

I have a work sheet that lists produts in alphabetical order.

The top of my work sheet displays the alphabet and each letter has a cell.

Under this row the letter A is and it has 7 blank rows under it - to
display
products beginning with the letter A and then the letter B is on the next
row
and this has 7 blank rows underneath it and it will store products
beginning
with B etc.

Can a function be set up to do the following. When I select a letter from
the alphabet on the top row say P, the page automatically goes to my list
of
products beginning with P.

If possible I would prefer not to use macros.

Any help would be much appreciated.

Thanking you in advance,

Bill

I have a work sheet that lists produts in alphabetical order.

The top of my work sheet displays the alphabet and each letter has a
cell.

Under this row the letter A is and it has 7 blank rows under it - to
display products beginning with the letter A and then the letter B is
on the next row and this has 7 blank rows underneath it and it will
store products beginning with B etc.

Can a function be set up to do the following. When I select a letter
from the alphabet on the top row say P, the page automatically goes to
my list of products beginning with P.


One way is to put this in A1:
=HYPERLINK(
"["&SUBSTITUTE(CELL("filename"),"[","")&"!A"&(8*COLUMN()-6),
CHAR(64+COLUMN()))
and then extend A1 to Z1.

The file has to be saved before the CELL function works.

(I have Excel 2003.)

Assuming your product list in Column A, and criteria in B1

In C1: =HYPERLINK("[#Book1.xlxs]Sheet1!A"&MATCH(B1&"*",A:A,0),B1)


"Bill" wrote:

Hi,

I have a work sheet that lists produts in alphabetical order.

The top of my work sheet displays the alphabet and each letter has a cell.

Under this row the letter A is and it has 7 blank rows under it - to display
products beginning with the letter A and then the letter B is on the next row
and this has 7 blank rows underneath it and it will store products beginning
with B etc.

Can a function be set up to do the following. When I select a letter from
the alphabet on the top row say P, the page automatically goes to my list of
products beginning with P.

If possible I would prefer not to use macros.

Any help would be much appreciated.

Thanking you in advance,

Bill

I made sure column A had cells with A-Z in them (single letters in each of those
26 cells).

Then I put this in B1:
=HYPERLINK("#A"&MATCH(CHAR(COLUMN(A1)+64),$A:$A,0) ,CHAR(COLUMN(A1)+64))

And I dragged it across to column AA (B-AA is 26 columns).


This portion of the formula
=CHAR(COLUMN(A1)+64)
is just a quick way to create the letters A-Z.


Bill wrote:

Hi,

I have a work sheet that lists produts in alphabetical order.

The top of my work sheet displays the alphabet and each letter has a cell.

Under this row the letter A is and it has 7 blank rows under it - to display
products beginning with the letter A and then the letter B is on the next row
and this has 7 blank rows underneath it and it will store products beginning
with B etc.

Can a function be set up to do the following. When I select a letter from
the alphabet on the top row say P, the page automatically goes to my list of
products beginning with P.

If possible I would prefer not to use macros.

Any help would be much appreciated.

Thanking you in advance,

Bill

--

Dave Peterson