3

I have 2 cells containing the same picklist values on separate sheets. I want the cells to mirror each other so that whenever I choose a new value in either sheet 1 or sheet 2, the value on the other sheet is updated to match the newly selected value. Is this possible?

UPDATE

  • Worksheet 1:

    • Worksheet Name: 'Live Forecast Tool'
    • Cell containing picklist: C3:D3 (merged)
    • Picklist values within range: ='SF MRF'!H5:Y5
  • Worksheet 2:

    • Worksheet Name: 'AH'
    • Cell containing picklist: C3:D3 (merged)
    • Picklist values within range: ='SF MRF'!H5:Y5

Cell C3:D3 needs to update to show the value that has been changed on the other sheet.

12
  • 1
    That answer may solve my question but obviously the application is different and more complex. I don't have a good enough knowledge of visual basic to tell or adapt the code. Which question should I reply to, to ask for assistance?
    – Alex S
    Nov 3, 2014 at 14:52
  • If you can provide a set of rules and actual cell references (including worksheet names) then someone can write a quick routine (there may actually be two) that can get you started. So far, your question is far too vague to even start. Append your question with the additional information so everyone can see it without having to search through several comments. Hint: a Workbook_SheetChange event macro could reduce the coding to a single event macro.
    – user385793
    Nov 3, 2014 at 15:14
  • @Jeeped Thanks for the advice, I've updated the question. I did a quick search for Workbook_SheetChange but couldn't see an obvious example of a macro that could be used for this, I'd appreciate any suggestions.
    – Alex S
    Nov 3, 2014 at 15:29
  • The solution would have to be something involving VBA. If you did it with formulas, you would have a circular reference.
    – fixer1234
    Nov 3, 2014 at 15:35

1 Answer 1

1

In the AH code area enter the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Set r1 = Range("C3")
    Set r2 = Sheets("Live Forecast Tool").Range("C3")
    If Intersect(Target, r1) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r2.Value = r1.Value
    Application.EnableEvents = True
End Sub

In the Live Forecast Tool code area enter the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Set r1 = Range("C3")
    Set r2 = Sheets("AH").Range("C3")
    If Intersect(Target, r1) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r2.Value = r1.Value
    Application.EnableEvents = True
End Sub
1
  • thanks, that's working perfectly. Since I posted the question I've added another sheet called AT. Is it possible to also mirror C3 for this sheet with the other two using this macro?
    – Alex S
    Nov 3, 2014 at 18:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .