Automated scheduling

[Beau the dog]

Good afternoon,

I'm trying to create an automated vehicle maintenance planner and saw this online, but couldn't see how they achieved it. This isn't my final solution, but might get me closer. How did they automatically plot the 'X' based on the interval and start date for each vehicle (with no VBA)? I've entered the X's manually.

13/12/2021​

20/12/2021​

27/12/2021​

03/01/2022​

10/01/2022​

17/01/2022​

24/01/2022​

31/01/2022​

07/02/2022​

14/02/2022​

21/02/2022​

28/02/2022​

07/03/2022​

14/03/2022​

21/03/2022​

28/03/2022​

04/04/2022​

11/04/2022​

18/04/2022​

25/04/2022​

02/05/2022​

09/05/2022​

Interval

Start

Vehicle 1

6 weekly

13/12/2021

X

X

X

X

Vehicle 2

8 weekly

13/12/2021

X

X

X

Vehicle 3

8 weekly

20/12/2021

X

X

X

Vehicle 4

12 weekly

27/12/2021

X

X

 

[JamesCanale]

An odd problem for the filter. I just made two.

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2
3		Interval	Start	12/13/2021	12/20/2021	12/27/2021	1/3/2022	1/10/2022	1/17/2022	1/24/2022	1/31/2022	2/7/2022	2/14/2022	2/21/2022	2/28/2022	3/7/2022	3/14/2022	3/21/2022	3/28/2022	4/4/2022	4/11/2022	4/18/2022	4/25/2022	5/2/2022	5/9/2022	5/16/2022	5/23/2022	5/30/2022	6/6/2022	6/13/2022	6/20/2022
4	Vehicle 1	6	12/12/2021	A						A						B						A						A
5	Vehicle 2	3	12/13/2021	A			A			B			A			A			B			A			A			B			A
6	Vehicle 3	8	12/20/2021		A								A								B								A
7	Vehicle 4	12	12/27/2021			A												A												B
8
9
10	filtered			1/24/2022
11	Vehicle 1	6	12/12/2021	A
12	Vehicle 2	3	12/13/2021	B
Sheet21

Cell Formulas
Range	Formula
E3:AE3	E3	=D3+7
D4:D7	D4	=IF(D$3>=$C4,IF(MOD((D$3-$C4)/7,$B4)<1,"A",""),"")
E4:AE7	E4	=IF(E$3>=$C4,IF(MOD((E$3-$C4)/7,$B4)<1,IF(MOD(SUMPRODUCT(--(LEN($D4:D4)>0)),3)=2,"B","A"),""),"")
A11:C12	A11	=(FILTER(A4:C7,INDEX(A4:AE7,,MATCH(D10,D3:AE3,0)+3)<>"",""))
D11:D12	D11	=(FILTER(INDEX(A4:AE7,,MATCH(D10,D3:AE3,0)+3),INDEX(A4:AE7,,MATCH(D10,D3:AE3,0)+3)<>"",""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D10	List	=$D$3:$AE$3

 

[Beau the dog]

Hello again, trying to push this on a bit further now. I've named your formulas to make it easier (I think). The formula D4 I've named "first_col" and for formula E4 I've named "remain_col"

I've also added a column after Column C, with the MOT expiry date (but made it the week commencing date). We represent MOT on the planner as "C". So lets assume our first expiry WC date is in cell D4 now. I entered the formula

=IF($D4=E$3,"C",first_col) and this works, for the columns after I enter the same except I change the value if false to remain_col. Great, I get my A,A,B mapped out, with a C dropped in for MOT.

My question is, and I think its just a alteration of your above formulas, is how do I get the the pattern to start A,A,B after there is a C.

So it should look like this for example A, A, B, A, A, C, A, A, B, A, A, B or A, A, B, A, C, A, A, B, A, A, B,

 

[Beau the dog]

Hi,

Maybe this one got lost in amongst all the others, just wondering if anyone has a solution to my last question in this thread please?

As always, any help is appreciated.

 

[JamesCanale]

Could you post your changes using xl2bb

 

[Beau the dog]

Could you post your changes using xl2bb

Apologies, I don't know what xl2bb is?

 

[JamesCanale]

It's the excel add-in available from the site (Look in FAQ). It's what most people use to post a spreadsheet segment (like what I post above). It's an easy way to send a functioning bit of spreadsheet to see what is happening.

 

[Beau the dog]

Ah, my companies IT won't allow add ins for excel