Update corresponding Cell if another cell changes data

[Patriot2879]

HI i hope you can help i need a formula to update a cell if changes are made, my range is A2:R18 , ROWS B2:R2 have dates in and columns A3:A18 have areas in, then B3:R18 have numbers in.

In Cell AC3 and AC4 this is a lookup for the date and area where it crossreferences and find the number in the corresing cell and put this number into Cell AC5. In AC6 this is where the number can be updated. What i want is then this to update the corresding cell from A2:R18. what formula could i use please to do this?

 

[Peter_SSs]

If I have understood correctly what you have, you won't be able to use a formula anywhere in B2:R18 to update from cell AC6.
Instead, you could try this Worksheet_Change event code in a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

I have assumed that
- AC3 contains a date matching one of the dates in B2:R2 and
- AC4 contains an area matching one of the areas in A3:A18 and
- AC6 is entered manually

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rDate As Range, rArea As Range
  
  If Not Intersect(Target, Range("AC6")) Is Nothing Then
    Set rDate = Range("B2:R2").Find(What:=Range("AC3").Value)
    Set rArea = Range("A3:A18").Find(What:=Range("AC4").Value)
    Application.EnableEvents = False
    Cells(rArea.Row, rDate.Column).Value = Range("AC6").Value
    Application.EnableEvents = True
  End If
End Sub

 

[Patriot2879]

HI would this work in multiple workbooks that are shared and will be open?

 

[Peter_SSs]

HI would this work in multiple workbooks that are shared and will be open?

It will only work on worksheets where you have inserted the code as described in steps 1 & 2 of my previous post.

 

[Patriot2879]

ok thank you, so if i put the code into 3 workbooks which are saved in different folders, and they were all open at the same time and updated, would they still work if linked together?

 

[Peter_SSs]

ok thank you, so if i put the code into 3 workbooks which are saved in different folders, and they were all open at the same time and updated, would they still work if linked together?

I don't know what you mean by "linked together" but the easiest way to answer that for sure would be to try it.