I have created a workbook where I want any individual employee to select a
supervisor and on a seperate worksheet it will show everyone that reports to
that person. Currently I have linked the two sheets and the supervisor name
appears in cell B1 however I can't get my data (all emplopyees) to
auto-filter down to just that supervisor. Is there anyway to do this? I'm
thinking it may be a VBA since I can not get the auto-filter to change just
based on B1. thanks!

On Jan 9, 2:22*pm, JAbels001
wrote:
I have created a workbook where I want any individual employee to select a
supervisor and on a seperate worksheet it will show everyone that reports to
that person. Currently I have linked the two sheets and the supervisor name
appears in cell B1 however I can't get my data (all emplopyees) to
auto-filter down to just that supervisor. Is there anyway to do this? I'm
thinking it may be a VBA since I can not get the auto-filter to change just
based on B1. thanks!

If you need to change a source cell, use a macro on a button to re-run
the filter. Probably Advanced Filter would be better, this is what
it's designed to do. You can just record a simple macro like this,
then tweak if necessary.

Well the source cell (B1) is automatically being filled by the selection on
the previous worksheet so I wouldn't need that to happen. I need my actual
data to change to only that supervisor's agents. I.E.- I need the data below
c3 to change when B1 is fulfilled.

A B C
1 ='Pick Agent'A1
2
3 Agent Supervisor

"Spiky" wrote:

On Jan 9, 2:22 pm, JAbels001
wrote:
I have created a workbook where I want any individual employee to select a
supervisor and on a seperate worksheet it will show everyone that reports to
that person. Currently I have linked the two sheets and the supervisor name
appears in cell B1 however I can't get my data (all emplopyees) to
auto-filter down to just that supervisor. Is there anyway to do this? I'm
thinking it may be a VBA since I can not get the auto-filter to change just
based on B1. thanks!

If you need to change a source cell, use a macro on a button to re-run
the filter. Probably Advanced Filter would be better, this is what
it's designed to do. You can just record a simple macro like this,
then tweak if necessary.

Not clear on how sheet2 is set up.

If you have in column B employee names and in column A their supervisor name
this should be easy to filter on a name entered in B1 on sheet1.

Sheet1 B1 has a Data Validation dropdown list with the supervisor's names.

Copy/paste the code below into the Sheet1 module. Right-click on the sheet
tab and "View Code" to open the module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS2 As Worksheet
Dim WS1 As Worksheet
Dim rng As Range
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
Set rng = WS2.Range("A1:B" & Rows.Count)
If Target.Address = "$B$1" And Target.Value < "" Then
Application.EnableEvents = False

WS2.AutoFilterMode = False 'clears filter
rng.AutoFilter Field:=1, Criteria1:=WS1.Range("B1") 're-filters

End If
Application.EnableEvents = True
End Sub

Alt + q to return to the Excel window.

Select a supervisor from the dropdown in B1 to filter Sheet2


Gord Dibben MS Excel MVP

On Fri, 9 Jan 2009 12:22:18 -0800, JAbels001
wrote:

I have created a workbook where I want any individual employee to select a
supervisor and on a seperate worksheet it will show everyone that reports to
that person. Currently I have linked the two sheets and the supervisor name
appears in cell B1 however I can't get my data (all emplopyees) to
auto-filter down to just that supervisor. Is there anyway to do this? I'm
thinking it may be a VBA since I can not get the auto-filter to change just
based on B1. thanks!

Gord,

thanks for the help... I had to change a couple of things b/c I had the
values in different places, but it worked like a charm! thanks again!

"Gord Dibben" wrote:

Not clear on how sheet2 is set up.

If you have in column B employee names and in column A their supervisor name
this should be easy to filter on a name entered in B1 on sheet1.

Sheet1 B1 has a Data Validation dropdown list with the supervisor's names.

Copy/paste the code below into the Sheet1 module. Right-click on the sheet
tab and "View Code" to open the module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS2 As Worksheet
Dim WS1 As Worksheet
Dim rng As Range
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
Set rng = WS2.Range("A1:B" & Rows.Count)
If Target.Address = "$B$1" And Target.Value < "" Then
Application.EnableEvents = False

WS2.AutoFilterMode = False 'clears filter
rng.AutoFilter Field:=1, Criteria1:=WS1.Range("B1") 're-filters

End If
Application.EnableEvents = True
End Sub

Alt + q to return to the Excel window.

Select a supervisor from the dropdown in B1 to filter Sheet2


Gord Dibben MS Excel MVP

On Fri, 9 Jan 2009 12:22:18 -0800, JAbels001
wrote:

I have created a workbook where I want any individual employee to select a
supervisor and on a seperate worksheet it will show everyone that reports to
that person. Currently I have linked the two sheets and the supervisor name
appears in cell B1 however I can't get my data (all emplopyees) to
auto-filter down to just that supervisor. Is there anyway to do this? I'm
thinking it may be a VBA since I can not get the auto-filter to change just
based on B1. thanks!

Good to hear.


Gord

On Fri, 9 Jan 2009 13:48:19 -0800, JAbels001
wrote:

Gord,

thanks for the help... I had to change a couple of things b/c I had the
values in different places, but it worked like a charm! thanks again!

"Gord Dibben" wrote:

Not clear on how sheet2 is set up.

If you have in column B employee names and in column A their supervisor name
this should be easy to filter on a name entered in B1 on sheet1.

Sheet1 B1 has a Data Validation dropdown list with the supervisor's names.

Copy/paste the code below into the Sheet1 module. Right-click on the sheet
tab and "View Code" to open the module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS2 As Worksheet
Dim WS1 As Worksheet
Dim rng As Range
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
Set rng = WS2.Range("A1:B" & Rows.Count)
If Target.Address = "$B$1" And Target.Value < "" Then
Application.EnableEvents = False

WS2.AutoFilterMode = False 'clears filter
rng.AutoFilter Field:=1, Criteria1:=WS1.Range("B1") 're-filters

End If
Application.EnableEvents = True
End Sub

Alt + q to return to the Excel window.

Select a supervisor from the dropdown in B1 to filter Sheet2


Gord Dibben MS Excel MVP

On Fri, 9 Jan 2009 12:22:18 -0800, JAbels001
wrote:

I have created a workbook where I want any individual employee to select a
supervisor and on a seperate worksheet it will show everyone that reports to
that person. Currently I have linked the two sheets and the supervisor name
appears in cell B1 however I can't get my data (all emplopyees) to
auto-filter down to just that supervisor. Is there anyway to do this? I'm
thinking it may be a VBA since I can not get the auto-filter to change just
based on B1. thanks!