Go to cell - Query

[Bill]

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

 

[Leekohr]

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

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.

 

[Don Guillett]

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 tab>view code>insert this>change
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

 

[MyVeryOwnSelf]

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.)

 

[Teethless mama]

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

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

 

[Dave Peterson]

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.