Changing spill cell values based on values of other cells in spill range

[superstan2310]

Hi All,

For the sake of not giving away the details of what I'm working on, let's say I have the below spill range. Where 1 is a cell that matches my criteria, and 0 is a cell that doesn't match my criteria.
What I am trying to do is make it so that the cells that are two rows below the "1" cells change their value from 0 to 2. For example, I have made the 0's I want changing in the below picture, red.
I need this to be dynamic as the spill range will expand over time. The closest I have gotten so far is through using SEQUENCE to give the spill cells a "row number" and saying if the row two above matches the criteria make the value equal to 2. However this leads to two issues:

1. The second row seems to think first row is two rows above, rather than one row above. I have this one sorted out.
2. The cells between the ones I want affected are ALSO being changed to 2, i.e. the values in blue.

Would anyone happen to know of a way to change the red 0's to 2's, without any other cells being affected?

 

[superstan2310]

It's not representative if I gave you a formula that worked on your sample but not your actual data.Trying to help you modify the formula so at least have the courtesy and tell us your actual expected outcome based on your actual data.

It's representative cause that is basically the formula I'm using, just with numbers as a substitute for words, if you took the sample of numbers I gave and put in words instead, it would pretty much be exactly what I'm using.

You can try the below :

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	G	A	D	A	G	A		1	0	1	0	1	0	1
2	I	G	I	G	F	F	G		0	0	0	0	0	0	0
3	C	G	B	B	E	B	I		2	0	2	0	2	0	2
4	I	H	I	B	E	C	F		0	0	0	0	0	0	0
5	A	B	A	H	A	I	A		1	0	1	0	1	0	1
6	G	I	H	H	B	H	H		0	0	0	0	0	0	0
7	B	B	I	I	B	G	E		2	0	2	0	2	0	2
8	E	H	D	I	B	F	D		0	0	0	0	0	0	0
9	A	B	A	H	A	H	A		1	0	1	0	1	0	1
10	G	I	C	I	D	G	B		0	0	0	0	0	0	0
11	D	H	D	I	F	D	E		0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
I1:O11	I1	=LET( data, A1:G11, matchA, --(data="A"), rows, ROW(data)-MIN(ROW(data))+1, cols, COLUMN(data)-MIN(COLUMN(data))+1, shifted, IF(rows<=ROWS(data)-2, INDEX(matchA, rows+2, cols), 0), result, matchA + 2 * shifted, result )
Dynamic array formulas.

Looks good, had to do some tinkering with it as row 11 on your example isn't showing any 2's, even though it should be.

But it doesn't seem to fully work as the "data" in this case is a dynamic spill range directly put into the formula itself, rather than being referred to from somewhere, so it doesn't technically have a row/column number according to Excel, so the ROW() and COLUMN() functions are returning #VALUE! errors. Admittedly I didn't think to mention that this was the ultimate goal as I didn't think it would end up making a difference.

It works if I put the "data" in a location first, and then refer to it, but if I just want to try and get it all as one formula and put the "data" formula straight into it, it just refuses to work.

Any chance there might be a way to get it to work as a single formula rather than referring elsewhere? If not I will see if I can try and find a place to sneakily put the data, although I imagine after a while the filesize will start getting a bit too big.

 

[Cubist]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	G	A	D	A	G	A		A	G	A	D	A	G	A
2	I	G	I	G	F	F	G		I	G	I	G	F	F	G
3	C	G	B	B	E	B	I		2	G	2	B	2	B	2
4	I	H	I	B	E	C	F		I	H	I	B	E	C	F
5	A	B	A	H	A	I	A		A	B	A	H	A	I	A
6	G	I	H	H	B	H	H		G	I	H	H	B	H	H
7	B	B	I	I	B	G	E		2	B	2	I	2	G	2
8	E	H	D	I	B	F	D		E	H	D	I	B	F	D
9	A	B	A	H	A	H	A		A	B	A	H	A	H	A
10	G	I	C	I	D	G	B		G	I	C	I	D	G	B
11	D	H	D	I	F	D	E		2	H	2	I	2	D	2
Sheet1

Cell Formulas
Range	Formula
I1:O11	I1	=LET(d,A1:G11,MAKEARRAY(ROWS(d),COLUMNS(d),LAMBDA(r,c,IF((r>2)*(INDEX(d,MAX(r-2,1),c)="A"),2,INDEX(d,r,c)))))
Dynamic array formulas.

 

[superstan2310]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	A	G	A	D	A	G	A		A	G	A	D	A	G	A
2	I	G	I	G	F	F	G		I	G	I	G	F	F	G
3	C	G	B	B	E	B	I		2	G	2	B	2	B	2
4	I	H	I	B	E	C	F		I	H	I	B	E	C	F
5	A	B	A	H	A	I	A		A	B	A	H	A	I	A
6	G	I	H	H	B	H	H		G	I	H	H	B	H	H
7	B	B	I	I	B	G	E		2	B	2	I	2	G	2
8	E	H	D	I	B	F	D		E	H	D	I	B	F	D
9	A	B	A	H	A	H	A		A	B	A	H	A	H	A
10	G	I	C	I	D	G	B		G	I	C	I	D	G	B
11	D	H	D	I	F	D	E		2	H	2	I	2	D	2
Sheet1

Cell Formulas
Range	Formula
I1:O11	I1	=LET(d,A1:G11,MAKEARRAY(ROWS(d),COLUMNS(d),LAMBDA(r,c,IF((r>2)*(INDEX(d,MAX(r-2,1),c)="A"),2,INDEX(d,r,c)))))
Dynamic array formulas.

This works perfectly. I'll need to look into how the LAMBDA() function works for the future.