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

superstan2310

New Member
Joined
Nov 3, 2024
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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?

1739625896125.png
 
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.
 
Upvote 0
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.

1739642394893.png
 
Upvote 0
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.

VBA Code:
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
ABCDEFGHIJKLMNOP
1
216141911111
39422733
487669992222
55656699
616191911111
75374697
844744772222
97267466
1017171311111
114727937
1232379372222
13
Sheet1
Cell Formulas
RangeFormula
I2:O12I2=fncReturn(A2:G12,1)
Dynamic array formulas.
 
Upvote 0
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
ABCDEFGHIJK
1
21010110101
30000000000
40000020202
50000000000
61010110101
70000000000
80000020202
Sheet2
Cell Formulas
RangeFormula
G2:K8G2=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.
 
Upvote 0
Is this what you mean?
Book1
ABCDEFGHIJK
1
21010110101
30000000000
40000020202
50000000000
61010110101
70000000000
80000020202
Sheet2
Cell Formulas
RangeFormula
G2:K8G2=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.
 
Upvote 0
Give a representative sample with text and the formula you entered.
 
Upvote 0
You can try the below :
Book4
ABCDEFGHIJKLMNO
1AGADAGA1010101
2IGIGFFG0000000
3CGBBEBI2020202
4IHIBECF0000000
5ABAHAIA1010101
6GIHHBHH0000000
7BBIIBGE2020202
8EHDIBFD0000000
9ABAHAHA1010101
10GICIDGB0000000
11DHDIFDE0000000
Sheet1
Cell Formulas
RangeFormula
I1:O11I1=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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top