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]

because other columns values were edited

What are the other columns and are they edited manually?
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Dani_LobP]

What are the other columns and are they edited manually?
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

So i have Template sheet, where i have a table:
- Column I where i have some values, 3 options manually input (1,2 or 3).
- Column M and N that both have either Yes or No situation. Formula here that based on some xlookup will make the Yes/No depending of conditions.
- Column O, formula that checks column I, and depending on what's there, will provide a result from 2 option: A or Bs. Depending on value in Column I, would check also M and N for further checks.
(so if i have in column I 1,2, or 3 options -> column O would be if option 1 then A, option 2 then B, and option 3 then would check if M and N has Yes/Yes, to make it be A, else, B)
Don't really think formula is relevant here but in short, thats more or less what it does.

So based on Column O, if there was something that made result change (either cuz I was updated manually, or M/N were also updated), then adjacent cell in column P would need to have a Yes. By default column P has No.

Hope it makes sense.

 

[mumps]

Please see my request in Post #2 regarding the attachment of a screenshot of your sheet or the uploading of your file.

 

[Dani_LobP]

I am not able to install XL2BB nor have possibility to use dropbox im afraid.
i can provide you some table as below hoping its enought ...

Column A	[...]	Column I	[...]	Column M	Column N	Column O*	Column P
		1		Yes	Yes	A	No
		3		Yes	No	B	No
		2		No	No	B	No
		1		No	Yes	A	No
		2		Yes	No	B	No
		3		Yes	Yes	A	No

*Formula in O:

=IF(I6=1,"A",IF(I6=2,"B",IF(AND(M6="Yes",N6="Yes"),"A","B")))

So as mentioned, Column I is manual input by other person, and can happen at any time, and columns M and N are formulas that change Yes/No based on some external extract I add to another tab, so I control when they will change.

The idea I'm trying to think of, is the possibility to have some script/macro, that when column O changes its value (because either column I, M or N changed its value), then overwrite column P and write a "Yes", just to have some way to track changes. That way i could filter "Yes" on column P and be able to know which lines were edited.

Hope this makes sense now. I saw some other answers and seems to be something possible to do, i just can't connect the dots from the scripts i saw to make it fit my specifics.
Also I am not sure if that would slowdown the file much.

Thanks in advance!

 

[NoSparks]

I have a table

Is this a true Excel table or a standard range?

if a true table...
what is the name of the table ?
what is the header in that column "O" ?
what is the name of the sheet this table is on ?

if a standard range...
what row does the data start on ?
what is the sheet name ?

 

[mumps]

columns M and N are formulas that change Yes/No based on some external extract I add to another tab

What are the formulas in columns M and N?

 

[Dani_LobP]

Is this a true Excel table or a standard range?

if a true table...
what is the name of the table ?
what is the header in that column "O" ?
what is the name of the sheet this table is on ?

if a standard range...
what row does the data start on ?
what is the sheet name ?

Its an Excel table.
I have some basics knowledge in VBA so unless the code is too complex, I was hoping to be able to edit table/column names in the potential solution provided for the right one.
but if the exact names will help or are required... then:
table name: MainData
Column O header: BSS input
Sheet name: BSS Template

Headers are actually in row 5, so data starts in row 6, but being a table i guess it doesn't matter.

Thanks!

 

[Dani_LobP]

What are the formulas in columns M and N?

Formulas in M and N are xlookup:
- in M it lookups in another tab, and if it result is bigger or equal to 1, then will be Yes, else, No.
- For column N is same as M, but when equal to 0 or empty, will be Yes, else No.
Not sure if exact formula is needed. Let me know.

Thanks!

 

[Dani_LobP]

i was thinking in something like this below, i found it in some other post while digging for info, but my knowledge is limited to fully understand the below or change it so it do as what i want.
Code in the sheet, worksheet option, and change also. and then the below:

'Private Sub Worksheet_Change(ByVal Target As Range)
'
'Dim tgtRng As Range
'
'Set tgtRng = Range("O:O")
'
'If Intersect(tgtRng, Target) Is Nothing Then Exit Sub
'
'Cells(Target.Row, 2).Value = vbNullString
'
'End Sub