Auto-fill problems

[GGarry]

Hi!

I'm having problems with auto-filling some complex formulas.

Example :
---------------------------------------------------------------------------------------------------------
=ALS(COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=2;$A$140;
ALS(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=2;$A$141;
ALS(COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=2;$A$142;
ALS(COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=2;$A$143;
ALS(COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=2;$A$144;
ALS(COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=2;$A$145;
ALS(COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=2;$A$146;
ALS(EN(COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=1);$A$147;
ALS(EN(COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=1);$A$148;
ALS(EN(COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=1);$A$149;
ALS(EN(COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=1);$A$150;
ALS(EN(COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=1);$A$151;
ALS(EN(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=1);$A$152;
ALS(EN(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=1);$A$153;
ALS(EN(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=1);$A$154;
ALS(EN(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=1);$A$155;
ALS(EN(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=1);$A$156;
ALS(EN(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=1);$A$157;
ALS(EN(COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=1);$A$158;
ALS(EN(COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=1);$A$159;
ALS(EN(COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=1);$A$160;
ALS(EN(COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=1);$A$161;
ALS(EN(COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=1);$A$162;
ALS(EN(COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=1);$A$163;
ALS(EN(COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=1);$A$164;
ALS(EN(COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=1);$A$165;
ALS(EN(COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=1);$A$166;ALS(EN(COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=1;COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=1);$A$167;
ALS(COUNTCOLOR(Lijst!$A$2;Lijst!P$8:Lijst!Q$8)=1;$A$168;
ALS(COUNTCOLOR(Lijst!$A$3;Lijst!P$8:Lijst!Q$8)=1;$A$169;
ALS(COUNTCOLOR(Lijst!$A$4;Lijst!P$8:Lijst!Q$8)=1;$A$170;
ALS(COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)=1;$A$171;
ALS(COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)=1;$A$172;
ALS(COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)=1;$A$173;
ALS(COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)=1;$A$174))))))))))))))))))))))))))))))))))
----------------------------------------------------------------------------------------------------------------

I need all the "Lijst!P$8:Lijst!Q$8" to autofill to "Lijst!R$8:Lijst!S$8","Lijst!T$8:Lijst!U$8", etc..
I've made an extra list going from "Lijst!A$8:Lijst!B$8" to "Lijst!Y$8:Lijst!Z$8", but when I autofill he gives me some random sequences.
My filters are off, the "automatic calculating" box is marked.
So I don't get why it won't work. Can you help me?

 

[ukmikeb]

Hi

Welcome to MrExcel Forum.

You didn't say which direction the formula was being autofilled , assuming down try changing Lijst!P$8:Lijst!Q$8 to INDEX(OFFSET(Lijst!P$8:Q$8;;ROWS($1:1)-1)) or using the equivalent of INDEX and OFFSET.

If you are autofilling across then change ROWS to COLUMNS.

If COUNTCOLOR is a UDF then it might be worth changing that to give you your results.

hth

 

[GGarry]

Hi

Welcome to MrExcel Forum.

You didn't say which direction the formula was being autofilled , assuming down try changing Lijst!P$8:Lijst!Q$8 to INDEX(OFFSET(Lijst!P$8:Q$8;;ROWS($1:1)-1)) or using the equivalent of INDEX and OFFSET.

If you are autofilling across then change ROWS to COLUMNS.

If COUNTCOLOR is a UDF then it might be worth changing that to give you your results.

hth

Thank you for responding,

I'm attempting to fill it to the side .
COUNTCOLOR is indeed a UDF, but not made by myself and I do not posses the needed skills to change it correctly, I was able to change te name from Countcolor to CC, given your adjustments, I now have too many characters in my cell.
Do you happen to have any further ideas, because I am at my wits end.

 

[ukmikeb]

Hi

How about using a helper column/s to provide the results to the COUNTCOLOR Formulae ie :-

=COUNTCOLOR(Lijst!$A$2;[COLOR=#ff0000]Lijst!P$8:Lijst!Q$8[/COLOR])
 =COUNTCOLOR(Lijst!$A$3;[COLOR=#ff0000]Lijst!P$8:Lijst!Q$8[/COLOR])
 =COUNTCOLOR(Lijst!$A$4;[COLOR=#ff0000]Lijst!P$8:Lijst!Q$8[/COLOR])
 =COUNTCOLOR(Lijst!$A$5;Lijst!P$8:Lijst!Q$8)
=COUNTCOLOR(Lijst!$D$3;Lijst!P$8:Lijst!Q$8)
=COUNTCOLOR(Lijst!$D$4;Lijst!P$8:Lijst!Q$8)
=COUNTCOLOR(Lijst!$D$5;Lijst!P$8:Lijst!Q$8)

then those formulae can use my suggestion of :_

INDEX(OFFSET(Lijst!P$8:Q$8;;(COLUMNS($1:1)-1)*2))

NB the earlier formula has been corrected to reference the correct columns
and can now be dragged right.

Then for your first part of the formula for the values of 2 you could say something like :-

=IFERROR(INDEX(Lijst!$A$140:$A$146;MATCH(2,$AA$1:$AA$7;0)); the remaining tests here)

using cells AA1 to AA7 change cell references as required.

or replace your original formula as :-

=ALS(AA$1=2;$A$140;
 ALS(AA$2=2;$A$141;
 ALS(AA$3=2;$A$142;
 ALS(AA$4=2;$A$143;
 ALS(AA$5=2;$A$144;
 ALS(AA$6=2;$A$145;
 ALS(AA$7=2;$A$146;
 ALS(EN(AA$3=1;AA$1=1);$A$147;
 ALS(EN(AA$4=1;AA$1=1);$A$148;  .....etc

and that formula could be dragged across too.

That will make your formulae a lot simpler.

hth