Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Filtering data (Excel 2003)

    Hi loungers....I am looking for some info re: filtering...I want to know if the DATA | FILTER | AUTO-FILTER can be used on rows that are hidden....I am attaching a small file...it's purpose is to track hiring dates, years of service, pension vesting dates and retirement dates (either voluntary or mandatory)...columns W:AO are set up to show Dec 31 and June 30 of each year, and I would like to be able to filter the data in A:V relative to the dates of June 30 and Dec 31 (of each year) so that I can easily forecast at any 6 month interval over the next few years how many people will be eligible to retire (without penalty b/c their service + age >=80) or will hit mandatory retirement age of 70....I was going to put the dates in W:AO in each column of T and V and then hide the rows and try to filter using the Dec 31 or June 30 dates but it seems that if a row is hidden, it won't show up when I apply a filter.....any ideas will be better than what I have so far..
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering data (Excel 2003)

    You might approach the task something like this:

    Basics:
    Insert a colum after col U and use this col to find the earliest date of col T or U by using the MIN function.
    Then I would SORT on this new col.

    Frill:
    You could insert an additional col and formula that returned the number of months until the possible retirement date.
    You could use conditional formating to color months 1-6; 7-12 etc
    If the number of employees got significantly large, you could use a COUNTIF function that would count the number in each 6-month group

    BTW: Filtering a list is basically the equivalent of hiding rows that don't match your criteria, hence filtering on hidden rows may not produce the desired results.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filtering data (Excel 2003)

    I am a little confused as to what you want to do exactly. Autofilter hides rows so there is no need to manually hide rows. You can mark rows that you want "hidden" by creating a column and marking them with an "x" and then filtering on the ones without an X (or blank) and then have alternate filter(s).

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Filtering data (Excel 2003)

    Hi Steve and GoCush....I think that I have been able to figure this out, based on why you posted, but I can't seem to get the AutoFilter to work if I protect that wsheet...this is the code in the macro:
    Sub Reformat()
    '
    ' Reformat Macro
    ' Macro recorded 6-11-2008 by David J. McNab
    '

    '
    ActiveSheet.Unprotect
    Cells.Select
    Selection.Columns.AutoFit
    Columns("K:L").Select
    Selection.EntireColumn.Hidden = True
    Columns("P:Q").Select
    Selection.EntireColumn.Hidden = True
    Columns("U:U").Select
    Selection.EntireColumn.Hidden = True
    Cells.Select
    Selection.Rows.AutoFit
    Rows("17:28").Select
    Selection.RowHeight = 40
    Range("C1112").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowSorting:=True, AllowFiltering:=True
    End Sub

    ..so it unprotects, runs this 'reformat' and re-protects...the cells containing the 'filter arrows' are not protected but when I try to use autofilter, it says the sheet is protected...I have used this in the past, even with a password--protected wsheet....any idea why if won't let me filter?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filtering data (Excel 2003)

    I don't know how your sheet is setup but if I put autofilter on a sheet and run your code, it works fine allowing it to autofilter...

    Autofilter on a protected sheet is not enabled by unprotectiing the cells with the arrows. It is done using the "Allowfilter" option in the protection which your code has and so I an unsure why it does not work.

    Perhaps you can post an example file with any proprietary info removed...

    Steve

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Filtering data (Excel 2003)

    Hi Steve....I can do that, altho I should ask this first: the filtering arrows will work (ie: I can filter rows etc)...it is the Sort Ascending, Sort Descending that isn't working, even though I have AllowSorting = True as part of my macro....is it something else I am missing or would you like me to post a small sample of the actual workbook?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filtering data (Excel 2003)

    Are all the cells in the range to sort unlocked? They must be unlocked (allowing users to edit the cells!) to be able to be sorted.

    AllowSorting allows sorting of unlocked cells in a protected worksheet. It does not allow sorting locked cells...

    You could create you own sort routine which first unprotected the sheet, sorted, then reprotected the sheet if you need to sort locked cells...

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Filtering data (Excel 2003)

    Some are locked and some are unlocked, depending on whether there is a formula in the cell....so, I'll probably have to unlock them ? in order to sort using the filtering arrows, or else, write a macro that will sort everything,,,,,I sort of wanted to be able to sort ascdning or descending using the arrows....but I guess I can't ...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filtering data (Excel 2003)

    Do you have formulas interspersed with values in columns or do you have columns of formulas and columns of values?

    If the latter, move the formulas outside the sorting range and keep them locked. Since they are formulas they should recalculate based on the current values in the row and thus even though they will not "literally sort", they will get new values based on the cells that do sort...

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Filtering data (Excel 2003)

    ...yes...I have colums of each mixed together, so I suppose if I re-do the wkbk to avoid that, I should be able to SORT using the filtering arrows....??..thank you for that tip.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •