Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On my spreadsheet, I record annual leave, sick absence, training away-days etc for a whole department. I would like to be able to show only the columns with for example an 'AL' for annual leave on some given row, so I can easily see which dates are holiday for that person (row). This should work just like filtering rows on some value in one column. Excel won't do this by itself, and copying, transposing and filtering is too clumsy. Any help TIA
    Frank

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Can you post a sample sheet? Use fake names.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Frank,

    How about this?

    Edit: This could be modified to request the value to filter on so you could select SL, HL, Etc.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Retiredgeek, this almost works - the trouble is where the range of columns is being counted. It works perfectly for Moe, as he is on holiday on day 10. For other staff, the rows up to the last AL are hidden if blank, but rows after that seem not to be selected. So for example days 3, 5, 7, 8, 9,10 remain un-hidden for Manny (with day 7 being his last AL day).

    My spreadsheet has a set of columns for name, room number, extension, work team etc etc., then columns are labeled for every day of the year, cells being completed for annual leave, sick absence, study days, trips out of office and a dozen more, with a 2-letter code for each, ie AL. SK, SD and so on.

    I'm looking to choose one such code, maybe SK for sick absence, then just hide all the other 'day of the year' columns, so I can see all the dates when that person is sick and record them for HR to have.

    The column range will be the same in every case, Jan 1 is column AA and Dec 31 is (I think) OA or OB in a leap year.

    This kind of thing might be really useful for a lot of people, I can think of several other applications in my job.

    Many thanks again. Frank

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    This isn't a complete solution, but a technique that may be useful.

    Put a formula in, say, row 1 that returns an error if that particular column should be hidden. If you do that, then you just need a couple of lines of code, I think:

    Cells.EntireColumn.Hidden = False
    Rows("1:1").SpecialCells(xlCellTypeFormulas, xlErrors).EntireColumn.Hidden = True


    Not sure that I follow exactly what's needed, but it sounds like the OFFSET() function would probably be useful in the formula - to return #N/A if the applicable cell below contains "AL" ??

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Frank,

    Ok I think this fixes it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    Just took another look at this. Attachment illustrates what I meant in my earlier post - about using a formula to drive whether or not the column is hidden. The reason for using #N/A, by the way, is that then you can use the xlErrors parameter of the SpecialCells function.
    In the attached I made it that clearing inpCode (cell C7) will cause all columns to be unhidden.
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Colin and RG,
    I've not been able to make either of your suggestions work yet, but don't fully grok the code. I'm spending a bit of time this evening on it and will report back with news. Many thanks for all you help. Frank

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Frank,

    Here's a modified version that picks up the value in the selected cell as the filter value so it will work for any type of leave.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by frankwalsh1962 View Post
    On my spreadsheet, I record annual leave, sick absence, training away-days etc for a whole department. I would like to be able to show only the columns with for example an 'AL' for annual leave on some given row, so I can easily see which dates are holiday for that person (row). This should work just like filtering rows on some value in one column. Excel won't do this by itself, and copying, transposing and filtering is too clumsy. Any help TIA
    Frank
    Have you investigated Custom Views, in 2007 they are on the View Ribbon. Does what I think you want without any code.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Catherine, thanks for this but custom views won't do the job. They only save a pre-chozen set of show/hide columns and I need to have excel choose which ones to hide according to changing criteria.
    I'm still trying with the suggestions from RG and may have time to try again over the weekend. Thanks Frank

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Frank,
    I'm curious as to what didn't work with the attachment in my last post (Attendance.xlsm). I noticed that I saved it as an Excel 2007 workbook, but presumbaly you would have said if that was the problem.
    Quite often something can be [primarily] accomplished with either Excel formulas or with VBA code. I tend to favour the former, since it's generally more maintainable/understandable to end users. And also more visible, of course.
    Anyway, what I posted *does* have a kind of funky formula in the row at the top. It's designed to return #N/A if that particular column should be hidden. The VBA code itself is then very short (and runs quickly), since it's just initially unhiding all the columns and then hiding just those columns where the aforementioned formula evaluates to #N/A. I say quicker because it does the hiding all at once - without having to iterate through the columns (no offense, RG!).
    Colin

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Colin,

    None Taken. Another view is always welcome!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Colin,
    I can see how your solution works now. I was not able to unhide all columns after using the refresh button, by (I thought) keeping the name field blank - hoping to restore the sheet to it's original state.
    I can always just close without saving of course. One useful feature is that the columns either side of the dated columns block are unaffected - I shall shortly upload a dummey example showing why this is good.

    Wishing you all a happy holiday season.
    Frank

Posting Permissions

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