Auto-fill problems

GGarry

New Member
Joined
Nov 23, 2016
Messages
2
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?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
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
 
Last edited:

GGarry

New Member
Joined
Nov 23, 2016
Messages
2
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

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

How about using a helper column/s to provide the results to the COUNTCOLOR Formulae ie :-
Code:
=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 :_
Code:
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 :-
Code:
=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 :-
Code:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,778
Messages
5,598,027
Members
414,204
Latest member
Excelmee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top