Automatic Update of formulas in the Adjacent cells

[ShAzeem]

Hi Everyone,
I have been trying to figure out if the following is possible. Even tried ChatGPT for hours.
Following is the format of data I am working on:

	A	B	C	D	E	F	G
1​	22​	33​	44​		55​	66​	77​
2​	12​	20​	10​		15​	35​	40​
3​	10​	13​	34​		40​	31​	37​

Note: Column D is empty.

Cell A3 has the formula "=A1-A2" and similarly cell B3 and so on have the similar formulas. What I am trying to accomplish is to find a way where when I change the formula in Cell A3 manually to let's say "=A1+A2", it automatically triggers the change of formulas accordingly in the other adjacent cells, Cell B3, C3 and so on.

Hope the requirement is clearly explained. Any sort of a solution, either in VBA or a script or functions and formulas are welcome.

Thanks and regards,
Azeem

 

[Joe4]

Welcome to the Board!

I think you need to clarify/further define the conditions under which this functionality is to occur.

Do you only want this to happen when a formula in column A is updated?
Do you only want this to happen on certain rows?
What if a cell in column A is updated with a hard-coded value?

 

[ShAzeem]

Thank you for your message.

1) Yes, this only needs to trigger when a formula in Column A is updated.
2) Yes, there are other rows where this needs to happen. but in the other rows it will happen only if the cell in column A of that row is changed.
3) If there is anything hard coded in the cell in Column A, it should definitely not trigger any change. Or if possible, we can have a kind of a switch which triggers this functionality on or off before we input any hard coded values.

Let me know if any further clarification is required.

Thanks once again.

Azeem

 

[Joe4]

OK, right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this VBA code in the VB Editor box that pops up.
This should do what you want automatically, as long as you have Macros/VBA enabled:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lc As Long

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Exit if update is NOT made to column A
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    
'   Only proceed if cell has a formula
    If Target.HasFormula Then
'       Find last column in row with entry
        lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
'       Copy formula from column A to last column
        Application.EnableEvents = False
        Target.Copy Range(Cells(Target.Row, 2), Cells(Target.Row, lc))
        Application.EnableEvents = True
    End If
        
End Sub

 

[ShAzeem]

Awesome. Works perfectly.

Just one thing. Can we not define the range of columns where it should update the formulas. Such as from column B-E and then from column J-P and so on instead of finding the last column in that row with an entry.

Thank you once again.

 

[Joe4]

Awesome. Works perfectly.

Just one thing. Can we not define the range of columns where it should update the formulas. Such as from column B-E and then from column J-P and so on instead of finding the last column in that row with an entry.

Thank you once again.

Sure. That could would just look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub

'   Exit if update is NOT made to column A
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    
'   Only proceed if cell has a formula
    If Target.HasFormula Then
'       Copy formula from column A to columns B-E and J-P
        Application.EnableEvents = False
        Target.Copy Range(Cells(Target.Row, "B"), Cells(Target.Row, "E"))
        Target.Copy Range(Cells(Target.Row, "J"), Cells(Target.Row, "P"))
        Application.EnableEvents = True
    End If
        
End Sub

 

[ShAzeem]

Fantastic. Thank you so much. That was quick.

Greatly Appreciated.

 

[Joe4]

You are welcome.
Glad I was able to help!