write formula in work sheet

supriyamk · Mar 2nd 2021

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)),"")

**Carim** · Mar 2nd 2021

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 "

supriyamk · Mar 2nd 2021

sir how to write that formula using code editor window.

i hope now u got it..

**Carim** · Mar 2nd 2021

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

supriyamk · Mar 2nd 2021

Ok sir thank you, I will try

**Carim** · Mar 2nd 2021

You are welcome ...

Good Luck

supriyamk · Mar 3rd 2021

sir i tried but its not working

**Carim** · Mar 3rd 2021

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

ActiveCell.FormulaR1C1 = _
        "=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

write formula in work sheet

Participate now!

Share