Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Amending a formula (Excel 2003 Service Pack 2)

    Hello

    I have downloaded an example workbook from this site and have been using it OK but I now need to change some of the parameters, this current code which is in all of the date cells

    IF(WEEKDAY(H$6,2)>5,"",IF(ISNUMBER(MATCH(H$6,Publi cHoliday,0)),"PH",INDEX(LeaveTypes,SUMPRODUCT(($G7 =SNames)*(SFrom<=H$6)*(STo>=H$6)*SType))))

    Allows entry of dates from Monday to Friday and the cells are then coloured by conditional formatting, if I change IF(WEEKDAY(H$6,2)>5 to IF(WEEKDAY(H$6,2)>7 I can allocate leave to weekends but the weekend cells which were previousely greyyed out are now white, I suspect this has something to do with the conditional format parameters in conjuction with the formula (please see formatting example), can somebody please advise me what I need to change to accomplish this, it would be nice also if I could do the same with Public Holidays

    Thank you

    Roger
    Attached Images Attached Images

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

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Welcome to Woody's Lounge!

    The condition WEEKDAY(H$6,2)>7 is always FALSE for WEEKDAY returns a number in the range of 1 through 7.

    It would be helpful if you could attach a small sample workbook with some dummy data and the formulas (max size 100 KB, zip if necessary).

  3. #3
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Hello

    Thank you for replying, I have not been rude in replying, I have been trying to make the workbook less than 100kb but I cannot seem to do that, I have deleted 2 sheets that would not be necessary for this request, I have deleted all rows from A3 onwards, all unecessary formulas, Macros, userforms, buttons and have left one row of headers and one row of data but it is still 133kb, I will keep trying and come back.

    Thank you

    Roger

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

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Have you zipped the resulting workbook?

  5. #5
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Hello

    Sorry I did not realise you could add zip files as well, this is very stripped down but I think it retains enough information to address the original questions

    Thank you

    Roger
    Attached Files Attached Files

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

    Re: Amending a formula (Excel 2003 Service Pack 2)

    I don't have time for an exhaustive analysis now, but if one of the conditions is not met, SUMPRODUCT(($G7=SNames)*(SFrom<=H$6)*(STo>=H$6)*ST ype) evaluates to 0. This causes theINDEX function to return an array with all values. I don't think you intended that.

    If nobody else does, I'll look at your workbook again later today.

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

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Does this do what you want? (Formula for H7, can be filled to the right)
    <code>
    =IF(WEEKDAY(H$6,2)>5,"",IF(ISNUMBER(MATCH(H$6,Publ icHoliday,0)),"PH",IF(AND(H$6>=SFrom,H$6<=STo),$D7 ,"")))</code>

  8. #8
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Hello

    Thank you for your effort but all this seems to have done is to strip out the formatting that exists to show the public holidays and weekends, in case I have not explained myself properly

    a) The original design of the form was to record absence for staff that worked Mon-Fri, weekends and Public Holidays are shown by Conditional Formatting

    My Company works 4 on and 4 of shifts so whilst I would like the formula to show the leave type on any weekend days or public holidays I still also need to see by way of conditional formatting where the weeekends and public holidays are.

    Thank you

    Roger

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

    Re: Amending a formula (Excel 2003 Service Pack 2)

    I'm afraid that I don't understand what you want. Could you attach a version of your workbook that shows what you want the result to be (by manual formatting)?

  10. #10
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Hello

    Thanks for your attention.

    Perhaps this 'mocked up' picture will show better what i require

    Thank you

    Roger
    Attached Images Attached Images

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

    Re: Amending a formula (Excel 2003 Service Pack 2)

    See attached version; I changed both the formula and the conditional formatting. Hope this does what you want.
    Attached Files Attached Files

  12. #12
    Lounger
    Join Date
    Feb 2008
    Location
    Chertsey, Surrey, United Kingdom
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Amending a formula (Excel 2003 Service Pack 2)

    Hello

    Thanks for your excellent help, I am having a few problems with implementation in my main workbook but I will have a few more tries tonight and maybe take the liberty of asking for more advice if I get stuck later.

    Thank you

    Roger

Posting Permissions

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