Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-08-2015, 02:55 AM
SarahL SarahL is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Mac OS X Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office for Mac 2011
Novice
Setting autofilter criteria using columns and cells relative to the active cell in another sheet
 
Join Date: Jun 2015
Posts: 4
SarahL is on a distinguished road
Default Setting autofilter criteria using columns and cells relative to the active cell in another sheet

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

Last edited by SarahL; 06-08-2015 at 03:03 AM. Reason: Adding clarification
Reply With Quote
  #2  
Old 06-08-2015, 03:01 AM
SarahL SarahL is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Mac OS X Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office for Mac 2011
Novice
Setting autofilter criteria using columns and cells relative to the active cell in another sheet
 
Join Date: Jun 2015
Posts: 4
SarahL is on a distinguished road
Default

Sorry, I can't get the formatting of the data tables to work. I hope it makes sense.
Reply With Quote
  #3  
Old 06-08-2015, 10:21 AM
charlesdh charlesdh is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Windows 7 32bit Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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"

Last edited by charlesdh; 06-08-2015 at 10:21 AM. Reason: Add information.
Reply With Quote
  #4  
Old 06-09-2015, 04:12 AM
SarahL SarahL is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Mac OS X Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office for Mac 2011
Novice
Setting autofilter criteria using columns and cells relative to the active cell in another sheet
 
Join Date: Jun 2015
Posts: 4
SarahL is on a distinguished road
Default

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
Attached Files
File Type: xlsm Test reporting tool.xlsm (54.3 KB, 5 views)
Reply With Quote
  #5  
Old 06-09-2015, 11:27 AM
charlesdh charlesdh is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Windows 7 32bit Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 06-09-2015, 12:44 PM
SarahL SarahL is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Mac OS X Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office for Mac 2011
Novice
Setting autofilter criteria using columns and cells relative to the active cell in another sheet
 
Join Date: Jun 2015
Posts: 4
SarahL is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 06-10-2015, 12:11 PM
charlesdh charlesdh is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Windows 7 32bit Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 06-11-2015, 10:26 AM
charlesdh charlesdh is offline Setting autofilter criteria using columns and cells relative to the active cell in another sheet Windows 7 32bit Setting autofilter criteria using columns and cells relative to the active cell in another sheet Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

HI,

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting autofilter criteria using columns and cells relative to the active cell in another sheet 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
Setting autofilter criteria using columns and cells relative to the active cell in another sheet 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
Setting autofilter criteria using columns and cells relative to the active cell in another sheet 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft