write formula in work sheet

  • i need this formula in sheet how do i write this??

    =IFERROR(IFERROR(LOOKUP(2,1/((All_Leave!$F$2:$F$100>=C$7)*(All_Leave!$E$2:$E$100<=C$7)*(All_Leave!$A$2:$A$100=$A8)),All_Leave!$G$2:$G$100),LOOKUP(2,1/((attendence!$B$2:$B$295=$A8)*(INT(attendence!$D$2:$D$295)=INT(C$7))),attendence!$I$2:$I$295)),"")

  • Hello,


    Difficult to understand your question ...?(


    You seem to indicate this formula works fine and does produce the expected result ...


    So is preventing you from ... as you say ... " writing the formula in the worksheet " :/

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    I do not know how familiar you are with Excel ...


    But, if you have enabled the Developer tab ... you can click on the button : Record Macro


    and type in your formula ...


    You will get the "macro translation" of your formula ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • OK ...


    Have no idea at all of what you are trying to accomplish ...


    But if you turn on your macro recorder .... you will get

    Code
    1. ActiveCell.FormulaR1C1 = _
    2. "=IFERROR(IFERROR(LOOKUP(2,1/((All_Leave!R2C6:R100C6>=R7C[-8])*(All_Leave!R2C5:R100C5<=R7C[-8])*(All_Leave!R2C1:R100C1=R[3]C1)),All_Leave!R2C7:R100C7),LOOKUP(2,1/((attendence!R2C2:R295C2=R[3]C1)*(INT(attendence!R2C4:R295C4)=INT(R7C[-8]))),attendence!R2C9:R295C9)),"""")"

    Now ... the main questions do remain ...


    Is this formula working properly in your sheet ?


    Why do you need to have it in a macro ?


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)