dynamic hyperlink or onclick event
 

I know how I would do the following in Access but I've been asked to do it in
Excel since it has to be distributed via email. Any help is greatly
appreciated.

I have an Access database that exports two queries to an excel spreadsheet.
In the excel spreadsheet is a main page that has a list of values. I want to
click on the value and it take me to the first query filtered by that value.
Then I want to be able to Click on a value in the filtered list and it take
me to the second query filtered by the value I just clicked on. Something
like below

Main page
ColA
1
2
3

1st query
ColA Col B
1 A
1 B
1 C
2 A
3 B
3 C

2nd Qry
ColA ColB
A yyy
A ZZZ
C XXX
C ZZZ

I want to click on 3 on the main page and it take me to the 1st query
showing only
ColA ColB
3 B
3 C

I then want to click on the C and it give take me to the 2nd query showing
only
ColA ColB
C XXX
C ZZZ

In Access I would do an OnClick event in on a form in Datasheet view but is
there anyway to do the in Excel. The report would have to be ran each week
and the data will change so a hard coded hyperlink won't work. Any
suggestions?

TIA
Tasha

dynamic hyperlink or onclick event
 

I would use the following code for a doubleclick event

Right-click on the 'Main Page' tab and select code. Paste the
following

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Sheets("1st Query").Select
Selection.AutoFilter Field:=1, Criteria1:=Target.Value

End Sub

Select the '1st Query' sheet in the VB Editor and paste this code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Sheets("2nd Query").Select
Selection.AutoFilter Field:=1, Criteria1:=Target.Value

End Sub

This assumes that either the title or data is in A1 for each query tab.
All a user now needs to do is double-click on the data to be filtered by
the next query. YOU may want to add some error checking but this should
get you started.

HTH


--
richardreye
------------------------------------------------------------------------
richardreye's Profile: http://www.excelforum.com/member.php...o&userid=14103
View this thread: http://www.excelforum.com/showthread...hreadid=345743

dynamic hyperlink or onclick event
 

Perfect! TY!!


"richardreye" wrote:


I would use the following code for a doubleclick event

Right-click on the 'Main Page' tab and select code. Paste the
following

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Sheets("1st Query").Select
Selection.AutoFilter Field:=1, Criteria1:=Target.Value

End Sub

Select the '1st Query' sheet in the VB Editor and paste this code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Sheets("2nd Query").Select
Selection.AutoFilter Field:=1, Criteria1:=Target.Value

End Sub

This assumes that either the title or data is in A1 for each query tab.
All a user now needs to do is double-click on the data to be filtered by
the next query. YOU may want to add some error checking but this should
get you started.

HTH


--
richardreye
------------------------------------------------------------------------
richardreye's Profile: http://www.excelforum.com/member.php...o&userid=14103
View this thread: http://www.excelforum.com/showthread...hreadid=345743