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?

 

[HighAndWilder]

As any cell value in a spill range is the result of a formula, it cannot be changed manually.

Are you able to provide some dummy data and the SPILL formula as a change to the formula and maybe the data may give you the result that you need?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[superstan2310]

As any cell value in a spill range is the result of a formula, it cannot be changed manually.

Are you able to provide some dummy data and the SPILL formula as a change to the formula and maybe the data may give you the result that you need?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The below is as basic of a summary that I can get.
There's a bunch of different values in a range (this range is normally on a different sheet and will get bigger over time).
I use a formula to say "If any cell in this range meets the criteria, show that value, otherwise be blank".
Now what I'm trying to do is to take this spill range and make it so a cell with a known relative position from the non-blank cells (the example I used before was two rows below) has a specific value of my choosing (example from before being 2).
So basically I'm trying to figure out how to add some sort of IF or whatever function to the spill range formula to basically say "If the cell two rows above this cell is not blank, display this other specified value, otherwise keep doing what you were already doing".
Again, without giving any further details, the actual data is in text form, and the "2" I wish to add is also actually text. I'm just using numbers here as they are easier to look at.

 

[HighAndWilder]

The below is as basic of a summary that I can get.
There's a bunch of different values in a range (this range is normally on a different sheet and will get bigger over time).
I use a formula to say "If any cell in this range meets the criteria, show that value, otherwise be blank".
Now what I'm trying to do is to take this spill range and make it so a cell with a known relative position from the non-blank cells (the example I used before was two rows below) has a specific value of my choosing (example from before being 2).
So basically I'm trying to figure out how to add some sort of IF or whatever function to the spill range formula to basically say "If the cell two rows above this cell is not blank, display this other specified value, otherwise keep doing what you were already doing".
Again, without giving any further details, the actual data is in text form, and the "2" I wish to add is also actually text. I'm just using numbers here as they are easier to look at.

View attachment 122404

All I can think of is calling a function to return an array with changed values which then spills.

The range is passed to the function.

Any logic can then be used in the function.

This example uses numbers but text can be used.

Public Function fncReturn(rng As Range, intKeep As Integer) As Variant
Dim arr() As Variant
Dim r As Integer
Dim c As Integer

On Error GoTo Err_Handler

  arr = rng.Value

  For r = 1 To UBound(arr)
    
    For c = 1 To UBound(arr, 2)
      If arr(r, c) <> intKeep Then
        arr(r, c) = ""
      End If
      If r >= 3 Then
        If arr(r - 2, c) = 1 Then
          arr(r, c) = 2
        End If
      End If
    Next c
      
  Next r
  
  fncReturn = arr
  
Exit_Handler:

  Exit Function

Err_Handler:

  fncReturn = "There has been a error."

  Resume Exit_Handler

End Function

Changing spill cell values based on values of other cells in spill range.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	1	6	1	4	1	9	1		1		1		1		1
3	9	4	2	2	7	3	3
4	8	7	6	6	9	9	9		2		2		2		2
5	5	6	5	6	6	9	9
6	1	6	1	9	1	9	1		1		1		1		1
7	5	3	7	4	6	9	7
8	4	4	7	4	4	7	7		2		2		2		2
9	7	2	6	7	4	6	6
10	1	7	1	7	1	3	1		1		1		1		1
11	4	7	2	7	9	3	7
12	3	2	3	7	9	3	7		2		2		2		2
13
Sheet1

Cell Formulas
Range	Formula
I2:O12	I2	=fncReturn(A2:G12,1)
Dynamic array formulas.

 

[Cubist]

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.

Is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	0	1	0	1		1	0	1	0	1
3	0	0	0	0	0		0	0	0	0	0
4	0	0	0	0	0		2	0	2	0	2
5	0	0	0	0	0		0	0	0	0	0
6	1	0	1	0	1		1	0	1	0	1
7	0	0	0	0	0		0	0	0	0	0
8	0	0	0	0	0		2	0	2	0	2
Sheet2

Cell Formulas
Range	Formula
G2:K8	G2	=LET(d,A2:E8,MAKEARRAY(ROWS(d),COLUMNS(d),LAMBDA(r,c,IF((r>2)*(INDEX(d,MAX(r-2,1),c)=1),2,INDEX(d,r,c)))))
Dynamic array formulas.

 

[superstan2310]

Is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	0	1	0	1		1	0	1	0	1
3	0	0	0	0	0		0	0	0	0	0
4	0	0	0	0	0		2	0	2	0	2
5	0	0	0	0	0		0	0	0	0	0
6	1	0	1	0	1		1	0	1	0	1
7	0	0	0	0	0		0	0	0	0	0
8	0	0	0	0	0		2	0	2	0	2
Sheet2

Cell Formulas
Range	Formula
G2:K8	G2	=LET(d,A2:E8,MAKEARRAY(ROWS(d),COLUMNS(d),LAMBDA(r,c,IF((r>2)*(INDEX(d,MAX(r-2,1),c)=1),2,INDEX(d,r,c)))))
Dynamic array formulas.

Yes, but I can't seem to make it work with text. But to be fair I have no clue how half of those functions work.

 

[Cubist]

Give a representative sample with text and the formula you entered.

 

[superstan2310]

Give a representative sample with text and the formula you entered.

I gave a representative sample in #3. Just switch the numbers for random words.

 

[Sanjeev1976]

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.

 

[Cubist]

I gave a representative sample in #3. Just switch the numbers for random words.

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.