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
 
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
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.
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.
 
Upvote 0
Book1
ABCDEFGHIJKLMNO
1AGADAGAAGADAGA
2IGIGFFGIGIGFFG
3CGBBEBI2G2B2B2
4IHIBECFIHIBECF
5ABAHAIAABAHAIA
6GIHHBHHGIHHBHH
7BBIIBGE2B2I2G2
8EHDIBFDEHDIBFD
9ABAHAHAABAHAHA
10GICIDGBGICIDGB
11DHDIFDE2H2I2D2
Sheet1
Cell Formulas
RangeFormula
I1:O11I1=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.
 
Upvote 1
Solution
Book1
ABCDEFGHIJKLMNO
1AGADAGAAGADAGA
2IGIGFFGIGIGFFG
3CGBBEBI2G2B2B2
4IHIBECFIHIBECF
5ABAHAIAABAHAIA
6GIHHBHHGIHHBHH
7BBIIBGE2B2I2G2
8EHDIBFDEHDIBFD
9ABAHAHAABAHAHA
10GICIDGBGICIDGB
11DHDIFDE2H2I2D2
Sheet1
Cell Formulas
RangeFormula
I1:O11I1=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.
 
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