Editing cell in multiple tab WITHOUT grouping

[cowboysnut]

Is there a way to edit cell M2 on Sheet1 where it updates on Sheet2 as well, but if I edit it on Sheet2 it updates it on Sheet1 WITHOUT grouping or selecting the tabs? Can I somehow specify that a certain cell is live and active through all sheets?

I currently have an equation on all sheets that ties back to my home indexing sheet and I update it there, but I would like to cut out the step of jumping back to the home sheet if I can.

 

[Jaafar Tribak]

Is there a way to edit cell M2 on Sheet1 where it updates on Sheet2 as well, but if I edit it on Sheet2 it updates it on Sheet1 WITHOUT grouping or selecting the tabs? Can I somehow specify that a certain cell is live and active through all sheets?

I currently have an equation on all sheets that ties back to my home indexing sheet and I update it there, but I would like to cut out the step of jumping back to the home sheet if I can.

If I am understanding correctly, you should be able to achieve this with some small code if it is ok for you to use Macros :

Code in the ThisWorkbook Module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If (Sh Is Sheet1 Or Sh Is Sheet2) And Not Intersect(Target, Range("M2")) Is Nothing Then
        Application.EnableEvents = False
        Sheet1.Range("M2") = Range("M2"): Sheet2.Range("M2") = Range("M2")
        Application.EnableEvents = True
    End If
End Sub

 

[Jaafar Tribak]

Or better use this code :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If (Sh Is Sheet1 Or Sh Is Sheet2) And Not Intersect(Target, Range("M2")) Is Nothing Then
        Application.EnableEvents = False
        Sheet1.Range("M2").Formula = Range("M2").Formula: Sheet2.Range("M2").Formula = Range("M2").Formula
        Application.EnableEvents = True
    End If
End Sub

 

[cowboysnut]

That's along the right lines, but I described it a little more simply than what I actually have. It's a document with maybe 30 sheets in it so that logic would become extraordinarily bulky if I were to try using that method if I am understanding it correctly.

 

[Jaafar Tribak]

That's along the right lines, but I described it a little more simply than what I actually have. It's a document with maybe 30 sheets in it so that logic would become extraordinarily bulky if I were to try using that method if I am understanding it correctly.

Do you need this to work on ALL sheets or just on 30 sheets out of all the sheets located in the workbook ? ie: Does the workbook contain 30 sheets or more ? and Is this always going to be Cell M2 for each sheet or are there other cells involved as well ?

 

[cowboysnut]

I want this over all the sheets in the document. One sheet (Index) has them in a different location (K4) while all the others will be the same location (P2). If I must, I can try to get them all at the same location but I'd prefer leaving everything where it is. The use of macros is perfectly fine. Thanks for your help.

 

[Jaafar Tribak]

I want this over all the sheets in the document. One sheet (Index) has them in a different location (K4) while all the others will be the same location (P2). If I must, I can try to get them all at the same location but I'd prefer leaving everything where it is. The use of macros is perfectly fine. Thanks for your help.

See if this works for you.

In the following code, I am assuming the name of the one worksheet with the K4 location is Sheet3 . I have assigned the sheet name to the String Constant IRREGULAR_SHEET at the very start of the Change event routine so that you can amend it easily as required.


Code in the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Const IRREGULAR_SHEET As String = "Sheet3" [B][COLOR=#008000]'<== Change this specific Sheet name as required.[/COLOR][/B]
    Dim oSh As Worksheet, oRange As Range
    
    On Error GoTo Xit
   
    Application.EnableEvents = False
    If Sh.Name = IRREGULAR_SHEET And Not Intersect(Target, Range("K4")) Is Nothing Then
        Set oRange = Range("K4")
    ElseIf Sh.Name <> IRREGULAR_SHEET And Not Intersect(Target, Range("P2")) Is Nothing Then
        Set oRange = Range("P2")
    Else
        GoTo Xit
    End If
    
    For Each oSh In Me.Worksheets
        If oSh.Name = IRREGULAR_SHEET Then
            oSh.Range("K4").Formula = oRange.Formula
        Else
            oSh.Range("P2").Formula = oRange.Formula
        End If
    Next oSh
Xit:
    Application.EnableEvents = True
End Sub

 

[cowboysnut]

This does not seem to be outputting anything for me. I've tried playing with the sheet names with no success. I tried it without the "ByVal Sh As Object, ByVal Target As Range" after the title so I could run the debugger and it crashed on "Me.Worksheets" I'm not too familiar with that kind of declaration but from trying to look it up it might be correct. I admit to not being the strongest on coding, but if I am following this correctly it shows a lot of potential!

 

[Jaafar Tribak]

This does not seem to be outputting anything for me. I've tried playing with the sheet names with no success. I tried it without the "ByVal Sh As Object, ByVal Target As Range" after the title so I could run the debugger and it crashed on "Me.Worksheets" I'm not too familiar with that kind of declaration but from trying to look it up it might be correct. I admit to not being the strongest on coding, but if I am following this correctly it shows a lot of potential!

Can you upload a copy of your workbook (after removing any sensitibve info) to a file sharing site like Box.net or to other file sharing site and post a link here so I can take a look ?

 

[cowboysnut]

Can you upload a copy of your workbook (after removing any sensitibve info) to a file sharing site like Box.net or to other file sharing site and post a link here so I can take a look ?

https://drive.google.com/file/d/1WRbYfSHtxRoGUJX30n31opzkQYCCF5xp/view?usp=sharing

Download it from there. It doesn't work properly on Google's viewer. I scrubbed a lot of the file away and only created a few sheets, but right now if you are on the "Index" sheet and change the date range in K4 or L4 then all other tabs are slaves to it. My objective is to have it so if I change the date in P2 or Q2 on any of the individual tabs that all tabs would follow suit.