Excel Filtering multiple worksheets in one workbook

S

Sharon

Hi,

I have a workbook with 5 worksheets.
The first three columns of each sheet are identical and linked to the
appropriate cells in the first worksheet.
Is there a way that one can set a filter so that when the first worksheet is
filtered, that the other worksheets are also automatically and simultaneously
filtered??
There is not evaluating of cells, i.e. no sum required, and I have not been
able to find a way.
Please Help!!!!!
 
S

Sharon

Thanks Max,
Will try that over the weekend.

I already have formulas in the other cells to define drop down lists
dependent on other criteria; but will see if I can "juggle it around" a bit
:)
--
Sharon the Grateful


Max said:
Here's a non-array formulas play which utilizes
Tom Ogilvy's UDF** to extract the filter criteria into a cell

A sample construct is available at:
http://www.savefile.com/files/6660963
FilterKeyCol in Master n replicate Filter to 5 sheets.xls

Assume the "master" sheet is: X,
data in cols A to C, from row2 down
(headers in row1)

Let's assume the key sync is to be
with the autofilter selection in col A

In sheet: X,

Put in E1:
=IF(showfilter(A:A)="No Active
Filter","",SUBSTITUTE(showfilter(A:A),"=","")+0)

Put in D2:
=IF($E$1="",ROW(),IF(A2=$E$1,ROW(),""))
Copy D2 down to the last row of data
(Leave D1 empty)

Then in the 5 "slave" sheets: 1,2,3,4,5

With the same headers in A1:C1

Put in A2:
=IF(ISERROR(SMALL(X!$D:$D,ROW(A1))),"",
INDEX(X!A:A,SMALL(X!$D:$D,ROW(A1))))

Copy A2 to C2, then fill down
to the same extent of data as in X

The 5 slave sheets will "sync"
to display with the filter selection in col A in X

**To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy and paste the UDF (below) into the white space on the right [everything
within the dotted lines ("begin vba" to "end vba")]

Press Alt+Q to get back to Excel
Then in Excel, we can use Tom's UDF as, eg in F1: =showfilter(A:A)

'-- begin vba --
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range


If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
'-- end vba --

Sharon said:
Hi,

I have a workbook with 5 worksheets.
The first three columns of each sheet are identical and linked to the
appropriate cells in the first worksheet.
Is there a way that one can set a filter so that when the first worksheet is
filtered, that the other worksheets are also automatically and simultaneously
filtered??
There is not evaluating of cells, i.e. no sum required, and I have not been
able to find a way.
Please Help!!!!!
Click to expand...
Click to expand...
 
S

Sharon

Must be having a thicko life; as I can't get it to work when applied to my
criteria. in the two cells i get #name? both times.
Each column must be filterable, but i can't get the formula to work (even if
i copy it straight out of your file) with one column, let alone several (25,
to be precise).
plus (something i forgot to mention before), i also have to be able to
"autofilter" the other sheets of their own accord, and, when possible have
the filter also work in reverse. - maybe excel is not the right solution for
my problem!!??!!

Thanks for your help
--
Sharon the Grateful


Max said:
Here's a non-array formulas play which utilizes
Tom Ogilvy's UDF** to extract the filter criteria into a cell

A sample construct is available at:
http://www.savefile.com/files/6660963
FilterKeyCol in Master n replicate Filter to 5 sheets.xls

Assume the "master" sheet is: X,
data in cols A to C, from row2 down
(headers in row1)

Let's assume the key sync is to be
with the autofilter selection in col A

In sheet: X,

Put in E1:
=IF(showfilter(A:A)="No Active
Filter","",SUBSTITUTE(showfilter(A:A),"=","")+0)

Put in D2:
=IF($E$1="",ROW(),IF(A2=$E$1,ROW(),""))
Copy D2 down to the last row of data
(Leave D1 empty)

Then in the 5 "slave" sheets: 1,2,3,4,5

With the same headers in A1:C1

Put in A2:
=IF(ISERROR(SMALL(X!$D:$D,ROW(A1))),"",
INDEX(X!A:A,SMALL(X!$D:$D,ROW(A1))))

Copy A2 to C2, then fill down
to the same extent of data as in X

The 5 slave sheets will "sync"
to display with the filter selection in col A in X

**To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy and paste the UDF (below) into the white space on the right [everything
within the dotted lines ("begin vba" to "end vba")]

Press Alt+Q to get back to Excel
Then in Excel, we can use Tom's UDF as, eg in F1: =showfilter(A:A)

'-- begin vba --
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range


If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
'-- end vba --

Sharon said:
Hi,

I have a workbook with 5 worksheets.
The first three columns of each sheet are identical and linked to the
appropriate cells in the first worksheet.
Is there a way that one can set a filter so that when the first worksheet is
filtered, that the other worksheets are also automatically and simultaneously
filtered??
There is not evaluating of cells, i.e. no sum required, and I have not been
able to find a way.
Please Help!!!!!
Click to expand...
Click to expand...
 
M

Max

.. in the two cells i get #name? both times.
.. but i can't get the formula to work (even if
i copy it straight out of your file) with one column
Click to expand...

That's because you didn't / haven't installed Tom's UDF into *your book*
(UDFs won't work across books, they need to be installed in the same
book that the UDF formulas' going to be in)

Try installing the UDF into your book, and you'd get it to work
Each column must be filterable,
.. several (25, to be precise) plus (something i forgot to mention before),
i also have to be able to "autofilter" the other sheets of their own accord,
and, when possible have the filter also work in reverse. -
maybe excel is not the right solution for my problem!!??!!
Click to expand...

The above is clearly going to compound the difficulties <g>
Think I'm out of ideas to offer you. You might want to hang around
awhile in case others might drop by and offer their insights.
Good luck !
 
A

Art Farrell

Hi Sharon,

I may have a wrong interpretation but you could try the macros below and
see if they can be adapted to what you are doing.

I have used AdvancedFilter since it has more flexibility than AutoFilter.
You
put the criteria formula in one cell with a blank cell above it. Since you
mentioned 25 columns I put the criteria in AA2. To enter the criteria your
should group the sheets, i.e., click on sheet1, hold down the shift key and
click on the last sheet tab. Then in AA2 on sheet1 enter your criteria.In
the
sample I set up I entered:

=AND(OR(A2="them",A2=4),C2=6)

I put the macros in the same module and two buttons to actuate them on the
first sheet. The first one will filter per your criteria and the second will
show
all the data.

The macros will also work with your last request- to filter back. Just put
your
criteria in the sheet where you want to start, but group your sheets again
when you put the criteria in cell AA2.

Option Explicit
Dim n As Long
Dim wks As Worksheet

Sub filtFive()
n = 1
For Each wks In Worksheets
Sheets("Sheet" & n).Activate
Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("AA1:AA2"), Unique:=False
n = n + 1
Next
End Sub

Sub noFiltFive()
n = 1
For Each wks In Worksheets
Sheets("Sheet" & n).Activate
ActiveSheet.ShowAllData
n = n + 1
Next
End Sub


If the above isn't clear drop me a line and I'll see if I can improve it.

CHORDially,
Art Farrell


Sharon said:
Must be having a thicko life; as I can't get it to work when applied to my
criteria. in the two cells i get #name? both times.
Each column must be filterable, but i can't get the formula to work (even if
i copy it straight out of your file) with one column, let alone several (25,
to be precise).
plus (something i forgot to mention before), i also have to be able to
"autofilter" the other sheets of their own accord, and, when possible have
the filter also work in reverse. - maybe excel is not the right solution for
my problem!!??!!

Thanks for your help
Click to expand...
 
Top