Linked & Dynamic Cells Across Tabs

[Bkisley]

I have a main summary tab and then multiple additional tabs for other more drilled down summaries. My main summary tab has a drop down list of places. So when I click Place 1, my main summary and all additional tabs will now only show Place 1 results as all additional tabs are just linked back to the main tab. I know a slicer could be used and linked exactly like this, however I do not have the ability to use a pivot table based on how all the data is spread out / organized, so I need a workaround
My request is to find a way to be on any tab and update the Place without having to go back to my main summary tab each time. If I am on the 5th tab and want to toggle between results for Place 2 and Place 3 - I would need to go to main summary tab click Place 2 then back to 5th tab, see the results, back to main summary tab, Place 3, back to 5th tab, then see the results.

There has to be a better non slicer way. Please and thank you!

Let's just assume A1 is the "Place" that then pulls all the data. I need to be able to change A1 on whatever tab I am on so that it updates my other tabs as well versus having everything linked back to main summary tab.

 

[OaklandJim]

If I understand what you want it seems that you want to use what is called the worksheet change event in all worksheets that are involved. If a change to the dropdown in any affected sheet is detected then the worksheet change event goes to the other worksheets and sets the value for the specified place. For example, assume that the dropdown in the main tab is changed then code will have to determine whether user changed the dropdown then loops all other affected worksheets and sets the dropdown value in each.

See if THIS helps.

I recommend Googling 1. Excel VBA worksheet code names and 2. Excel worksheet-scoped names. See pictures in main tab that show those two concepts.

 

[OaklandJim]

The previous workbook demo had issues. I hope that this is not too technical but when using a worksheet's change event to modify another worksheet that also has a change event, you need to temporarily turn off events or you'll get an endless loop of worksheet change events firing. When done turn events back on. See code for worksheets in this NEWER WORKBOOK.

 

[OaklandJim]

Here is the change event for the Main worksheet. Similar ones are needed for each worksheet.

Option Explicit
'Worksheet change event for the worksheet whose code name is Main

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("Place")) Is Nothing _
     Then

'       Put the value entered by user into the other two worksheets,
'       into the cell whose worksheet scoped name is Place.

'       Also note use of "code names" for the worksheets. Using code names
'       is good if the worksheet/tab name might change. Referring to
'       a worksheet's code name -- rather than worksheet/tab name -- ensures
'       that the resepective worksheet is acessed by code even if the
'       worksheet/tab name changes. Referring to the worksheet's code name
'       is done using square brackets. In this case the ranges whose worksheet-scoped
'       name is Place are affected in the worksheets whose code names are
'       1. Summary 1 and 2. Summary 2.
'
        
        Application.EnableEvents = False ' <= turn off event handlers

        [Summary1].Range("Place").Value = Target.Value

        [Summary2].Range("Place").Value = Target.Value
        
        Application.EnableEvents = True ' <= turn on event handlers

    End If

End Sub

'Worksheet change event for the worksheet whose code name is Summary1
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("Place")) Is Nothing _
     Then
        
        Application.EnableEvents = False

        [Main].Range("Place").Value = Target.Value
        
        [Summary2].Range("Place").Value = Target.Value

        Application.EnableEvents = True

    End If

End Sub

Option Explicit

'Worksheet change event for worksheet whose code name is Summary2.
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("Place")) Is Nothing _
     Then

        Application.EnableEvents = False

        [Main].Range("Place").Value = Target.Value
        
        [Summary1].Range("Place").Value = Target.Value

        Application.EnableEvents = True

    End If

End Sub

 

[Bkisley]

Yes you understood correctly. Thank you!
I'm assuming there is no way to do something like this without code?

 

[OaklandJim]

No way that I an think of.

 

[Bkisley]

No way that I an think of.

Didn't think I could be that lucky I will go forward with this. Thank you so much!