# Posts by roligankan

• ## copy down formula issue

Hello,
Also posted this in: http://www.excelforum.com/exce…mula-issue.html?p=3364467

Anyway:
I have a problem with filling down a formula and using the index function

D3: =IF(AND(\$GZ\$4<=\$B3;\$B3<=\$HA\$4);'Sheet 2'!\$E\$7/\$HB\$4;0)

What I want in D4 is:

D4: =IF(AND(\$GZ\$4<=\$B4;\$B4<=\$HA\$4);'Sheet 2'!\$E\$7/\$HB\$4;0)

This is done simply by draging d3 down to d4,

But my issue is that i want this pattern horizontelly

E3: =IF(AND(\$GZ\$5<=\$B3;\$B3<=\$HA\$5);'Sheet 2'!\$E\$12/\$HB\$5;0)

E4: =IF(AND(\$GZ\$6<=\$B3;\$B3<=\$HA\$6);'Sheet 2'!\$E\$17/\$HB\$6;0)

and so on. I know that i should use the index function but i cant seem to get it to work, any solutions?

I am trying this:
D3: =IF(AND(\$GZ\$4<=\$B3;\$B3<=\$HA\$4);INDEX('Sheet 2'!\$E:\$E;ROWS(\$A\$1:A1)*5+2)/\$HB\$4;0)
But i think this only help the pattern \$E\$7, \$E\$12, \$E\$17

Best regards!

• ## Urgent!! Advanced formula filling

[INDENT]Hi!

I need to get this done for work and i can't seem to find how to do it.

In sheet 1:

c8: =IF(AND(C\$3>='Sheet 2'!\$N5;C\$3<='Sheet 2'!\$O5;'Sheet 2'!\$N7=\$C\$1);1;0)
c9: =IF(AND(C\$3>='Sheet 2'!\$N10;C\$3<='Sheet 2'!\$O10;'Sheet 2'!\$N12=\$C\$1);1;0)
c10: =IF(AND(C\$3>='Sheet 2'!\$N15;C\$3<='Sheet 2'!\$O15;'Sheet 2'!\$N17=\$C\$1);1;0)

I want to be able to drag down this formula to c200 following this pattern creating

c11: =IF(AND(C\$3>='Sheet 2'!\$N20;C\$3<='Sheet 2'!\$O20;'Sheet 2'!\$N22=\$C\$1);1;0)
c12: =IF(AND(C\$3>='Sheet 2'!\$N25;C\$3<='Sheet 2'!\$O25;'Sheet 2'!\$N27=\$C\$1);1;0)

Is it possible to drag down a pattern like this?