Excel formula help!

[strongman86]

Hi guys,

Need your help to sort this. I got this sheet below that I need tweaking those simple formulas. Data in columns F / G / H are populated manually. Following columns will have formulas in them on rows 3 / 4 / 5. Data in columns I and on wards will repeat itself. For example in row 3: (B3-YES , C3-YES , D3-YES) means that data in I3/J3/K3 etc. will repeat itself on every 4th column.

	A	B	C	D	E	F	G	H	I	J	K
1		ID	ID	ID		DAY	DAY	DAY	DAY	DAY	DAY
2	CODE	39	38	37		1	2	3	4	5	6
3	1	YES	YES	YES		37	39	38	=F3	=G3	=H3
4	2	YES	NO	YES		39		38	=E4	=F4	=G4
5	3	NO	YES	NO		37	37	37	=H5	=I5	=J5

Below is actual data:

I	J	K
DAY	DAY	DAY
4	5	6
37	39	38
	39
37	37	37

I am populating this simple sheet by selecting column K and dragging formulas to column L and so on and so forth. This will give me below cell values.

L	M	N	O	P	Q	R
DAY	DAY	DAY	DAY	DAY	DAY	DAY
7	8	9	10	11	12	13
37	39	38	37	39	38	37
38		39		38		39
37	37	37	37	37	37	37

This works OK for me until there is a change in ID/CODE range (B:D) as any one of the cells could change from YES to NO and vice versa.
I would really need formulas in column L (and following columns) to check range (B:D) and then populate accordingly.



For example if range (B:D) below would change to :

B	C	D
ID	ID	ID
39	38	37
NO	NO	YES
YES	YES	YES
YES	NO	YES

then this would generate below:

L	M	N	O	P	Q	R
DAY	DAY	DAY	DAY	DAY	DAY	DAY
7	8	9	10	11	12	13
37	37	37	37	37	37	37
37		38		39		37
39	37	39	37	39	37	39

Basically speaking every next column starting from L and on wards should contain one of the following ID: (39/38/37) which was not used for the longest time & if it's marked as YES, but ignore if states NO.
Hope this makes any sense?

Thank you.

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		ID	ID	ID		DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY
2	CODE	39	38	37		1	2	3	4	5	6	7	8	9	10	11	12
3	1	YES	YES	YES		39	38	37	39	38	37	39	38	37	39	38	37
4	2	YES	NO	YES		39		37	39		37	39		37	39		37
5	3	NO	YES	NO			38			38			38			38
Sheet5

Cell Formulas
Range	Formula
F3:Q5	F3	=IF(INDEX($B3:$D3,MOD(COLUMNS($F3:F3)-1,3)+1)="YES",INDEX($B$2:$D$2,MOD(COLUMNS($F3:F3)-1,3)+1),"")

 

[Eric W]

It occurred to me that you can use 2 much simpler formulas:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		ID	ID	ID		DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY
2	CODE	39	38	37		1	2	3	4	5	6	7	8	9	10	11	12
3	1	YES	YES	YES		39	38	37	39	38	37	39	38	37	39	38	37
4	2	YES	NO	YES		39		37	39		37	39		37	39		37
5	3	NO	YES	NO			38			38			38			38
Sheet5

Cell Formulas
Range	Formula
F3:H5	F3	=IF(B3="YES",B$2,"")
I3:Q5	I3	=F3

 

[strongman86]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		ID	ID	ID		DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY	DAY
2	CODE	39	38	37		1	2	3	4	5	6	7	8	9	10	11	12
3	1	YES	YES	YES		39	38	37	39	38	37	39	38	37	39	38	37
4	2	YES	NO	YES		39		37	39		37	39		37	39		37
5	3	NO	YES	NO			38			38			38			38
Sheet5

Cell Formulas
Range	Formula
F3:Q5	F3	=IF(INDEX($B3:$D3,MOD(COLUMNS($F3:F3)-1,3)+1)="YES",INDEX($B$2:$D$2,MOD(COLUMNS($F3:F3)-1,3)+1),"")

Hi Eric W,
It works, but the problem is when I change data in B3:D5 I need only need latest/furthest to the right column data to change, but it changes data in all reference cells.
I guess the only way would be to create macro?