When cell with formula changes -> edit adjacent cell

[Dani_LobP]

Hi all,

I want to know if its possible the below scenario:

I have a table, with some formulas here and there, but in column O, I have a formula with some conditions and dependent on other 3 columns in same table.
What I want to know, is, if I can have some kind of script that every time formula in O is refreshed and the result changes, to have in column P a Yes, and if nothing changed, leave it with No (as per default).

So basically I want to just have a Yes in P column, when the formula in O result changed (because other columns values were edited, making O formula bring different result).
O column cell formula starts in O6 , same as P columns, is P6 if that helps.

I tried to check around but some answers can't really fully understand.

By the way, is this a potential reason to make file slow? and, if formulas are set to manual, will macro only work once refresh happens?

thanks in advance!

 

[mumps]

The Worksheet_Change macro will not work because it is triggered by a manual change in the data not by the result of a formula. Please post the actual formulas that include the name of the other tab and the cell references. This information is needed if you want the change in column P to happen automatically.

 

[NoSparks]

Perhaps this will work, it's an adaptation of post #2 here

In a Regular Module

Public arr

In ThisWorkbook Module

Private Sub Workbook_Open()
    arr = ThisWorkbook.Worksheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    arr = ThisWorkbook.Worksheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
End Sub

I'm not sure why the SheetActivate is needed above or if it even is needed.

In the Sheet Module

Private Sub Worksheet_Calculate()
    Dim arrTemp, i As Long

    arrTemp = Sheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) <> arrTemp(i, 1) Then
            Sheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Cells(i).Offset(, 1) = "YES"
            Exit For    ' to not check any further
        End If
    Next
    arr = Sheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
End Sub

For this to work you must save, close and reopen the workbook.

 

[Dani_LobP]

The Worksheet_Change macro will not work because it is triggered by a manual change in the data not by the result of a formula. Please post the actual formulas that include the name of the other tab and the cell references. This information is needed if you want the change in column P to happen automatically.

Ok,
Formula in M is:

=IF(XLOOKUP(CONCAT(TRIM([@[RELTIO Product id]]),"MDR"),SAPreport[MaterialStockSegment],SAPreport[Unrestricted2],"")>=1,"Yes","No")

Formula in N is:

=IF(OR(XLOOKUP(CONCAT(TRIM([@[RELTIO Product id]]),"MDD"),SAPreport[MaterialStockSegment],SAPreport[Unrestricted2],"")=0,XLOOKUP(CONCAT(TRIM([@[RELTIO Product id]]),"MDD"),SAPreport[MaterialStockSegment],SAPreport[Unrestricted2],"")=""),"Yes","No")

Formula in O is:

=IF([@[Version allowed to be shipped MDD only, MDR only, both OR None?]]="MDR Only","MDR",IF([@[Version allowed to be shipped MDD only, MDR only, both OR None?]]="MDD Only","MDD",IF(AND([@[MDR released]]="Yes",[@[MDD depleted? (Y/N)]]="Yes"),"MDR","MDD")))

Hope this helps, thanks in advance!

 

[Dani_LobP]

Perhaps this will work, it's an adaptation of post #2 here

In a Regular Module

Public arr

In ThisWorkbook Module

Private Sub Workbook_Open()
    arr = ThisWorkbook.Worksheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    arr = ThisWorkbook.Worksheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
End Sub

I'm not sure why the SheetActivate is needed above or if it even is needed.

In the Sheet Module

Private Sub Worksheet_Calculate()
    Dim arrTemp, i As Long

    arrTemp = Sheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) <> arrTemp(i, 1) Then
            Sheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Cells(i).Offset(, 1) = "YES"
            Exit For    ' to not check any further
        End If
    Next
    arr = Sheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Value
End Sub

For this to work you must save, close and reopen the workbook.

thanks! i tried this, but unsure of what you mean with the first part.
what do you mean with "on a regular module" ? like, anywhere?

i create a module in the workbook, and just place that line alone?
if so, i get below error when run: error 13, missmatch. and debug line "For i = LBound(arr) To UBound(arr)" - which i beliebe arr thingy i did it wrong...

 

[NoSparks]

You can download my test file here and have a look.

 

[Dani_LobP]

You can download my test file here and have a look.

Thanks! seems to work now.
I had some typo on my side. but from what I see now it works as expected.

So from what I understand, the macro is always running, but if formulas are set to manual, it will only run when formulas are refreshed, right?
You know if this can slowdown the performance while working on the file? if more than 1-2 people work on the file, is it gonna crash or update correctly if it happens to need to be updated?

thanks again!

 

[NoSparks]

if formulas are set to manual, it will only run when formulas are refreshed, right?

right

You know if this can slowdown the performance while working on the file?

with the array comparison being done in memory I wouldn't think anything would be noticeable

if more than 1-2 people work on the file, is it gonna crash or update correctly if it happens to need to be updated?

with no way of testing this afraid I don't know

 

[Dani_LobP]

right

with the array comparison being done in memory I wouldn't think anything would be noticeable

with no way of testing this afraid I don't know

Hi NoSparks,

I've been testing so far and works as expected, until i got to a scenario that somehow brings an issue.

The table has many rows, and we have a column to make them be unique.
Problem is that when a changes happen in bulk, many rows change for the same item, it seems that only the first row will be edited with a Yes.
it should be placing a yes in all the rows that had a change.

Not sure if there is a way to add in the code you provided some connection to the column that has unique values or what is making it stop when changes happen after checking first row.

Hope it makes sense what i try to explain.

Thanks in advance!

 

[Dani_LobP]

@NoSparks not sure if you still get notifications here

 

[NoSparks]

Comment out the Exit For line

        If arr(i, 1) <> arrTemp(i, 1) Then
            Sheets("BSS Template").ListObjects("MainData").ListColumns("BSS input").DataBodyRange.Cells(i).Offset(, 1) = "YES"
            Exit For    ' to not check any further
        End If