Yearly Schedule Populate With and X

Post a sample workbook so we can see how your data is setup. Include how you would like the results to show.

Try this. Drag across/down:

Please Login or Register  to view this content.

Here are two formulae that work with your example workbook. I entered a row above the text months with the month numbers
Enter in B11 and fill across and down:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
9		1	2	3	4	5	6	7	8	9	10	11	12
10	Machine Number	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
11	3				x
12	4				x
13	5												x
14	9					x
15	13		x
16	14												x
17	16												x
18	17												x
19	18					x
20	19										x
21	20		x

Here is a way:

Formula:

Please Login or Register  to view this content.

OR

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I	J	K	L	M
8
9		1	2	3	4	5	6	7	8	9	10	11	12
10	Machine Number	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
11	3				L2
12	4				L2
13	5												L2
14	9					L1
15	13		L2
16	14												L2
17	16												L2
18	17												L1
19	18					L1
20	19										L2
21	20		L2
22	21	L2
23	22									L1
24	38				L1
25	40				L1
26	41			L2

The following will reverse the L1 to L2 and visa versa

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I	J	K	L	M
9		1	2	3	4	5	6	7	8	9	10	11	12
10	Machine Number	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
11	3				L1
12	4				L1
13	5												L1
14	9					L2
15	13		L1
16	14												L1
17	16												L1
18	17												L2
19	18					L2
20	19										L1
21	20		L1
22	21

You have likely entered a cell reference to the active cell by mistake. What I have shown you is an actual copy of my worksheet so you know that the formula does work.

I think that the solution to your problem lies in VBA in order to get the results into one set of months. However, I did come up with this solution that may work.
In Schedule!B7 enter this formula and Fill across to Schedule!M7 and then fill down:

Formula:

Please Login or Register  to view this content.

In Schedule!N7 enter this formula and fill across to Schedule!Y7 and then fill down:

Formula:

Please Login or Register  to view this content.

That is the problem that I couldn't work out. When a condition in a formula is met, the formula stops processing. The situation is set up so that the cells 6 months apart become interdependent and always want to be the opposite of the other. The first decision made by the formula is to enter the opposite of what is on the PM Electrical leakage worksheet.

I have just thought of something and if it works out I will get back to you.

This "appears" to work for the second half of the year but the rest of the year fails. (my brain is fried).
Enter this in B7 and fill across and down. Maybe you can see what I'm missing:

Formula:

Please Login or Register  to view this content.

Enter this in B7 and fill across and down:
=IF(AND(MONTH('PM Electrical leakage'!$L14)=B$5,'PM Electrical leakage'!$J14="A"),"S",IF(AND(MONTH('PM Electrical leakage'!$L14)=B$5,'PM Electrical leakage'!$J14="S"),"A", IF(LOOKUP(MONTH('PM Electrical leakage'!$L14)+6,{7,8,9,10,11,12,13,14,15,16,17,18;7,8,9,10,11,12,1,2,3,4,5,6})=B$5,'PM Electrical leakage'!$J14,"")))