Topic:excel autofilter to another sheet - please help Remainpoint:0
   
PostTime:12/16/2008 7:13:20 PM FloorTop
Lv is 1
Avatar
Level:
1
Professional point:
81
Experience:
32
Thread:
281
Post:
990
Total online time:
32M
Joined date:
4/29/2007 12:22:00 AM
Last Visit:
12/17/2008 12:54:30 AM
Status:
Offline
Sheet 1 "Renew" would a list of contracts needing to be renewed - ie. end date is 1 year away. Would be good if 6 months became highlighted in yellow, less than 6 months red.

And some way of indicating that the last extension has been taken, as this requires the whole tendering process to start again, best if a year is left to do this. Some items may also be critical and others less so.

Please help, much appreciated.
 
     
   
Gender PostTime:12/16/2008 8:31:17 PM Point:0 | Floor# 1
Lv is 1
portrait
Level:
1
Professional point:
95
Experience:
1
Thread:
268
Post:
947
Total online time:
1M
Joined date:
4/28/2007 11:29:00 PM
Last Visit:
12/17/2008 12:23:49 AM
Status:
Online
OK thanks.

I've just noticed that I need additional criteria, because a contract can be extended more than once. The start date, end date, and maximum extension date are always the same value, and extended to date is the one that changes.

> Contract Starts
ie. >
Start Date: 01/05/2007
End Date: 30/04/2010
Extended To: -
Max Extension: 30/04/2012

when 1st extended >
Start Date: 01/05/2007
End Date: 30/04/2010
Extended To: 30/04/2011
Max Extension: 30/04/2012

Then 2nd extension:
Start Date: 01/05/2007
End Date: 30/04/2010
Extended To: 30/04/2012
Max Extension: 30/04/2012

Currently these wouldn't show up.
 
     
   
Gender PostTime:12/16/2008 8:36:55 PM Point:0 | Floor# 2
Lv is 1
portrait
Level:
1
Professional point:
46
Experience:
1
Thread:
287
Post:
926
Total online time:
1M
Joined date:
4/28/2007 11:03:00 PM
Last Visit:
12/17/2008 12:22:57 AM
Status:
Offline
Hi there!

Can you please be a bit more specific? Perhaps with some details and examples?

When you say you want to use AutoFilter [filter] your data, how exactly do you want to filter? By what criteria do you want to filter? When you select a drop down? Where is this drop down? An in-cell drop down (data validation list)? Where will this drop down be located? With the right information, yes, this is very possible, and quite easy to setup.

When you want to highlight your data with conditional formatting, can you describe exactly all of your specifications and criteria? What you can do is select your entire column (click the 'E' column header) and go to Format \ Conditional Formatting. For your condition of being one year away (less than or equal to one year behind the current system date), use Formula Is and enter..

=E1<=TODAY()-365

Color as desired. For your next condition, you can use something like..

=E1<=TODAY()-182

If you, however, want to take out anything greater than or equal to today, you need to add a second condition. Insert this formula and put in the formula desired above...

=(FormulaFromAbove)*(E1<=TODAY())

HTH
 
     
   
Gender PostTime:12/16/2008 9:38:28 PM Point:0 | Floor# 3
Lv is 1
portrait
Level:
1
Professional point:
8
Experience:
13
Thread:
275
Post:
917
Total online time:
13M
Joined date:
4/28/2007 11:37:00 PM
Last Visit:
12/16/2008 11:49:17 PM
Status:
Offline
Sorry it's taken me so long to get back to you here.

To move data and filter programmatically via VBA you can use something like this ...

Code:
Option ExplicitConst DATEFORMAT As String = "dd/mm/yyyy"Sub FilterData()    Dim wsData As Worksheet, wsRenew As Worksheet    Dim rData As Range, rFilter As Range, lngRow As Long    Dim dtStart As Date, dtEnd As Date    Set wsData = ThisWorkbook.Sheets("Data")    Set wsRenew = ThisWorkbook.Sheets("Renew")    lngRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row    Set rData = wsData.Range("A1:I" & lngRow)    '////////////////////////////////////////////////////    '// ENTER DATE HERE /////////////////////////////////    '////////////////////////////////////////////////////    dtStart = CDate(Format(DateSerial(2007, 1, 4), DATEFORMAT))    '////////////////////////////////////////////////////    dtEnd = CDate(Format(DateSerial(Year(dtStart) + 1, Month(dtStart), Day(dtStart)), DATEFORMAT))    Application.ScreenUpdating = False    rData.AutoFilter field:=1, Criteria1:="BRS010"    rData.AutoFilter field:=5, Criteria1:=">=" & dtStart, Operator:=xlAnd, Criteria2:="<=" & dtEnd    Set rFilter = wsData.Range("A2:I" & lngRow).SpecialCells(xlCellTypeVisible)    If Not rFilter Is Nothing Then        wsRenew.Range("2:" & wsRenew.Rows.Count).ClearContents        rFilter.Copy wsRenew.Range("A2")        wsRenew.Cells.EntireColumn.AutoFit    End If    rData.AutoFilter    Application.CutCopyMode = False    Application.ScreenUpdating = TrueEnd Sub

HTH
 
     
   
Gender PostTime:12/16/2008 10:40:32 PM Point:0 | Floor# 4
Lv is 1
portrait
Level:
1
Professional point:
72
Experience:
24
Thread:
285
Post:
975
Total online time:
24M
Joined date:
4/29/2007 12:36:00 AM
Last Visit:
12/16/2008 11:54:20 PM
Status:
Offline
Apply the conditional formatting as I lined out in the second post to this thread. Just change the formulas slightly..

=(E1<=TODAY()-365)*(E1<>"")

.. and ..

=(E1<=TODAY()-182)*(E1<>"")

Make sure when you apply these you select the entire column and E1 is the activecell (that will be in the name box ("E1"), or just click the "E" header).

HTH
 
     
   
Gender PostTime:12/16/2008 11:46:11 PM Point:0 | Floor# 5
Lv is 1
portrait
Level:
1
Professional point:
78
Experience:
9
Thread:
286
Post:
947
Total online time:
9M
Joined date:
4/29/2007 12:17:00 AM
Last Visit:
12/16/2008 11:26:07 PM
Status:
Offline
Well when the contract needs renewing again on say 01/11/2010, because the end date is now greater than the current date ("01/11/2010") the filter won't pick it up, but it will need renewing.

Hope that makes sense.
 
     
   
Gender PostTime:12/16/2008 11:53:19 PM Point:0 | Floor# 6
Lv is 1
portrait
Level:
1
Professional point:
0
Experience:
14
Thread:
314
Post:
924
Total online time:
14M
Joined date:
4/24/2007 6:51:00 AM
Last Visit:
12/17/2008 12:02:44 AM
Status:
Offline
Infact I've now fixed those issues, so helpful thank you.

I'm gonna move onto Sorting & Conditional formatting, any tips or help appreciated.

Thought, I'd better post my solution:

Code:
Option ExplicitConst DATEFORMAT As String = "dd/mm/yyyy"Sub FilterData()    Dim wsData As Worksheet, wsRenew As Worksheet    Dim rData As Range, rFilter As Range, lngRow As Long    Dim dtToday As Date, dbStart As Double, dbEnd As Double    Set wsData = ThisWorkbook.Sheets("Data")    Set wsRenew = ThisWorkbook.Sheets("Renew")    lngRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row    Set rData = wsData.Range("A1:I" & lngRow)    '////////////////////////////////////////////////////    '// ENTER DATE HERE /////////////////////////////////    '////////////////////////////////////////////////////    dtToday = Date    dbStart = CDate(Format(DateSerial(Year(dtToday), Month(dtToday), Day(dtToday)), DATEFORMAT))    '////////////////////////////////////////////////////    dbEnd = CDate(Format(DateSerial(Year(dbStart) + 1, Month(dbStart), Day(dbStart)), DATEFORMAT))    Application.ScreenUpdating = False    'rData.AutoFilter field:=1, Criteria1:="*"    rData.AutoFilter field:=5, Criteria1:=">=" & dbStart, Operator:=xlAnd, Criteria2:="<=" & dbEnd    Set rFilter = wsData.Range("A2:I" & lngRow).SpecialCells(xlCellTypeVisible)    If Not rFilter Is Nothing Then        wsRenew.Range("2:" & wsRenew.Rows.Count).ClearContents        rFilter.Copy wsRenew.Range("A2")        wsRenew.Cells.EntireColumn.AutoFit    End If    rData.AutoFilter    Application.CutCopyMode = False    Application.ScreenUpdating = TrueEnd Sub

Change the Start and End Date to Double as it was still recognising them as formatted dates rather than a Serial date number.
 
Love my life!!
     
   
Gender PostTime:12/17/2008 12:27:47 AM Point:0 | Floor# 7
Lv is 1
portrait
Level:
1
Professional point:
0
Experience:
14
Thread:
314
Post:
924
Total online time:
14M
Joined date:
4/24/2007 6:51:00 AM
Last Visit:
12/17/2008 12:02:44 AM
Status:
Offline
I'm not really following you.
 
Love my life!!
     
   
Gender PostTime:12/17/2008 12:55:26 AM Point:0 | Floor# 8
Lv is 1
portrait
Level:
1
Professional point:
0
Experience:
14
Thread:
314
Post:
924
Total online time:
14M
Joined date:
4/24/2007 6:51:00 AM
Last Visit:
12/17/2008 12:02:44 AM
Status:
Offline
I guess one answer is you just repeat the 'query' of the macro for the extended to date.

And then for the Maximum extension do a seperate sheet with the 'query' on this field, as within 6 months needing a new tender.
OR directly under the current extended to, sorted and then highlighted a different colour.


But is there a better way?!
Your help is appreciated
 
Love my life!!
     
   
Gender PostTime:12/17/2008 12:59:01 AM Point:0 | Floor# 9
Lv is 1
portrait
Level:
1
Professional point:
94
Experience:
1
Thread:
261
Post:
990
Total online time:
1M
Joined date:
4/28/2007 11:41:00 PM
Last Visit:
12/17/2008 12:44:11 AM
Status:
Online
Np about wait, thats a great help thank you.

It needs a little more though, instead of picking one tender such as BRS0010, I want all tenders that need to be renewed in the next 12 months to show up on the renew sheet, and in the order in which they need to be renewed. I want to then add conditional formatting.

Basically I'm looking for excel to tell the user which ones need reviewing and extending, this currently is a problem, and so I'm attempting to sort it out.

Again thanks for the help.
 
     
1

Sorry, you are not login, click here to login

 

About us | Advertise | Contact us | Partner | Bug Report|Suggesting box|Donation
Home | Forum | Affiliate program| Remote help | Setting | Search | Document | Help | Download|Message

 

Start new topicAdvanced search