Setting autofilter criteria using columns and cells relative to the active cell in another sheet

SarahL · #1 06-08-2015, 02:55 AM

Hi,
I have a workbook that contains one sheet with a detailed transaction list, e.g.:

A B C D
1 Type Month Year Amount
2 Creditors 1 2015 10
3 Creditors 2 2015 12

4 Debtors 1 2015 15
5 Expenses 1 2015 20
6 Expenses 1 2015 30

On this sheet I have turned on the filter by column name

I have another sheet in the same workbook that summarises the detail data, e.g.:

A B C
1 31/01/2015 28/02/2015
2 Creditors 10 12
3 Debtors 15 0
4 Expenses 50 0

What I would like to do is either have the amounts in the summary page as hyperlinks that, when clicked, open up the first detail sheet and set the autofilter for Type, Month and Year based on the corresponding values from the summary sheet in row 1 (for month and year) and column A (for type). Or I would be happy to use a command button so that you select a cell in the summary sheet and then click on a button to open up sheet 1 with these filters applied.

Thanks

SarahL · #2 06-08-2015, 03:01 AM

Sorry, I can't get the formatting of the data tables to work. I hope it makes sense.

charlesdh · #3 06-08-2015, 10:21 AM

Hi,

Welcome to the forum.
It would help if you attached a file for us to look at.

Note:
I too use Excel for "Mac"

SarahL · #4 06-09-2015, 04:12 AM

Thank you charlesdh,

I have attached a test example. I have set up a command button in the sheet 'Overhead Analysis' which opens the sheet 'TranDetail' and sets some filter criteria but they are just fixed at the moment. I would like them to be set based on the corresponding values relative to the active cell in the Overhead Analysis sheet when the button is pressed.

Thanks

charlesdh · #5 06-09-2015, 11:27 AM

Hi,

In your sample you hard coded the filter. As I understand you want to click on as in your example "Accounting" and have the filter set in the detail sheet.
Question: How do you determine which month you need to look at?
Can you provide more information.

SarahL · #6 06-09-2015, 12:44 PM

Yes I hard coded some values in the macro auto filter just to test it could be done. Instead of those hard coded values I want to pass through the values from the overhead analysis sheet. If the user clicks on cell C6 for Accounting in April (value = 37) then the sheet should check the month and year from the date in cell C5 by using MONTH(C5) and YEAR(C5) because the value in cell C5 is a date that is formatted to look like a month.
I hope that's clear?
Thanks

charlesdh · #7 06-10-2015, 12:11 PM

Hi,

Here's a code that may work. However, it currently looks at "April" and "Accounting" only.
You mentioned "Value = 37" I do not know what you mean by that.
Copy and paste this code to the sheet module for "Overhead".

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
''Name variables
Dim MyMth As String
Dim MyYr As String
Dim MyCtr As String
Dim TDws As Worksheet
Dim OAws As Worksheet
Set TDws = Sheets("TranDetail")
Set OAws = Sheets("Overhead Analysis")
'''check to see if user clicks with in column "A"''
'' if not exit sub ''
If Intersect(Target, Range("A3:a23")) Is Nothing Then
    Exit Sub
Else
''' we set the filter for  TranDetail then ''''
''' activate TranDetail sheet'''
    MyMth = Month(OAws.Range("C5"))
    MyYr = Year(OAws.Range("C5"))
    MyCtr = OAws.Range("A" & ActiveCell.Row)
    ''' Activate TranDetail and set filter '''
    TDws.Activate
    On Error Resume Next '' check for filter if there goto next line of code''
    Selection.AutoFilter
    TDws.Range("A5:G5").Select '' we selected the range for the filter
    With Selection
        .AutoFilter Field:=2, Criteria1:=MyMth
        .AutoFilter Field:=3, Criteria1:=MyYr
        .AutoFilter Field:=5, Criteria1:=MyCtr
    End With
    
End If
End Sub

charlesdh · #8 06-11-2015, 10:26 AM

HI,

Forgot to mention you need to "Double" click on "Accounting for the code to run

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create a New Sheet from Existing Sheet with Specific Columns	malam	Excel Programming	1	10-17-2014 10:01 PM
Matching Criteria against a range when criteria is not in contiguous cells??	GMorris	Excel	9	08-20-2014 02:15 AM
Autofilter on two Date Columns	OTPM	Excel Programming	3	04-29-2014 12:56 AM
From an XL sheet ,how to keep the group of columns which match with other XL sheet	Zubairkhan	Excel	2	03-04-2014 10:57 PM
How to merge two cells from sheet1 to one cell in sheet2 in next sheet	KIM SOLIS	Excel	6	10-30-2011 11:14 PM