Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Leave Management - glitch (Excel 97)

    In the attached spreadsheet which I use for management of leave in our institution there is a conditional format that shades a cell yellow if anything is typed into it.

    All worked well until the boss wanted a way to indicated that a leave request had been submitted. I used the strike through to do this and it works well EXCEPT when I add a person it runs through a macro to reshuffle the staff into a particular order. But the strike through doesnt move with the staff member and their record.

    To see this happen, go to the Staff sheet and "Add New Staff" then look at the Jan 05 sheet and see what happens.

    Can anyone assist?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Leave Management - glitch (Excel 97)

    Conditional formatting is tied to the data, so it moves with the data. The strike through (a diagonal cell border) is applied as direct cell formatting, so it is NOT tied to the data. Sorting the range only sorts the cell contents, the directly applied borders stay where they are. Diagonal borders are not available in conditional formatting, so that's not an option.

    Unless you can decide on another format that could be applied as conditional formatting, you'd probably have to insert the new employee in the right place instead of adding it at the end, then sorting the worksheet. It might be tricky to get it exactly right.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Leave Management - glitch (Excel 97)

    Another idea could be to add a code value to the AL column. If the code is a 0, then your standard yellow shading can apply if something is typed into the cell, but if the code in AL is 1, (indicating that a leave request had been submitted), a third condition can make the font red or strike through!
    See modified attached file for example.
    Regards,
    Rudi

  4. #4
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Leave Management - glitch (Excel 97)

    Hi Rudi

    Thanks for your suggestion. It is almost perfect.

    The only trouble is that a person may have more than one group of leave in a month.

    Therefore submitting their written requests separately at different times. Your method turns all the cells text Red. Do you see what I mean?

    Kerry

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Leave Management - glitch (Excel 97)

    Hi Kerry,

    I did not realize this! I doubt that this approach will work then as conditional formatting only has 3 conditions. I suspect an macro that triggers on the worksheet_selectionchange event may be the best approach to automate the formatting.

    I do not quite understand your statement - "The only trouble is that a person may have more than one group of leave in a month." Can you elaborate on this?
    Regards,
    Rudi

  6. #6
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Leave Management - glitch (Excel 97)

    Hi again Rudi

    What I meant by

    "The only trouble is that a person may have more than one group of leave in a month." Can you elaborate on this?

    is ...... if Tim Jones for example has leave in January on 4,5,6 and 7th and then again on say 24 and 25th January, that is two instances of leave. It is not uncommon for the person to submit two their leave application forms at separate times.

    Just to clarify the how it works. All staff are asked to plan and submit their request for leave the the coming twelve months. They do this by email. This is then entered on the spreadsheet by the office administrator. When all requests have been received the management then look at all requests to see that there are not clashes. For example we dont want all the staff in one office to be on leave at the same time. Then if your leave is approved, about 2 weeks before you go on holiday you submit a leave request FROM. The for mis submitted through the office administrator who used the strike macro. This gave a quick visual to see that leave forms have been submitted.

    Hope this helps to understand.

    Kerry

  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: Leave Management - glitch (Excel 97)

    Extending Rudi's suggestion, instead of just 1 cell to indicate for the entire row, you could have 31 values (AL - BP) one for each "day" to indicate the "striketru"

    Instead of formatting that cell with a "strike thru", change the value in the other column (which affects the cond formatting).
    You would have to change the third condition to: (get rid of the "$" before the AL)
    =AND(NOT(ISBLANK(D5)),AL5=1)

    And change the "strikeout macros" to something like:
    <pre>Sub StrikeOut()
    ActiveSheet.Unprotect
    Selection.Offset(0, 34).Value = 1
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

    Sub Unstrike()
    ActiveSheet.Unprotect
    Selection.Offset(0, 34).Value = 0
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub</pre>


    You would also have to extend your sort range to include those other 31 columns

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Leave Management - glitch (Excel 97)

    Hi Steve

    I am kind of a little confused. I have had a go at your explanation but I know I am missing something in the interpretation. Can you look at what I have done.

    I am a bit confused about the fact that you have left in the strike through macro when apparently, according to Hans, that formatting will not sort???

  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: Leave Management - glitch (Excel 97)

    You still need as your second conditon, the AL5=0 (as Rudi had indicated)
    <pre>=AND(NOT(ISBLANK(D5)),AL5=0)</pre>


    Otherwise it never tests the 3rd condition

    Hans indicated that that your strikethru formatting would not sort. I modified Rudi's suggested conditional formatting...

    Steve

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Leave Management - glitch (Excel 97)

    Hi Kerry,
    Here is a version that sould do what you need. It is based on Steves suggestions in the previous post. Please test it and see if it is OK. (It is a little more clunky...but it does what you need...according to my understanding!)
    Let me know if it is OK.
    Regards,
    Rudi

  11. #11
    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: Leave Management - glitch (Excel 97)

    Your code does not strike/unstrike the selected cell but changes the selection to 0 or 1. My suggestion would strike/unstrike the selection by changing the cell in the other columns (which I would hide) to 0/1.

    I don't think the user really needs to see the 0/1 columns that are used for conditional formatting...

    Steve

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Leave Management - glitch (Excel 97)

    Hi Steve.
    The problem with the strike through formatting is that it did not sort (move) with the rows when Kerry sorted the rows. I am trying to avoid the border strike though, as this was the original problem in the file.
    I do agree that the 0/1 info is a little clunky and makes for a larger / complex look, but if Kerry is OK with it...it is working well. (It could always be shifted further right! )
    Regards,
    Rudi

  13. #13
    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: Leave Management - glitch (Excel 97)

    The version I proposed (based on your original) uses conditional formatting that looks at the 0/1 in the "hidden columns" to the right. These columns would be included in the sort so the conditional formatting would be maintained.

    The difference between my suggestion and your attachment is that in mine, the user select the cell to format and the appropriate column to the right is changed. In yours the user must edit in 2 places, the "original" and the "hidden columns" to the right. I (pesonally) would prefer that the user never see or work (directly) with those "hidden columns".

    In mine you have selected the cell to format, in yours you do not...

    Steve

  14. #14
    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: Leave Management - glitch (Excel 97)

    Another suggestion is instead of 2 buttons: strike and unstrike, you could have 1 called "Toggle Strike" assigned to the macro:

    <pre>Sub ToggleStrikeOut()
    ActiveSheet.Unprotect
    Selection.Offset(0, 34).Value = _
    1 - Selection.Offset(0, 34).Value
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub</pre>


    This would toggle between the 2 modes.

    Steve

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Leave Management - glitch (Excel 97)

    I adjusted it Steve, and you are right...It works and looks a WHOLE lot better!

    Kerry, thanx to Steves suggestions....here is a GREAT solution. (ENJOY) <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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