CODEWITHSUNDEEP
excelvba
user3408365
user3408365

Reputation: 1

Excel sheets dependant on / mirroring each other

Let's say I have three sheets in Excel. Sheet 1 is the Master sheet, sheets 2 and 3 contain different sets of information with the same headers that feed into the table in the master sheet. Is there a way to make it such that I could edit information in sheet 1 and sheet 2 will change AND vice versa so I can edit info in sheet 2 that will update the master sheet?

Upvotes: 0

Views: 237

Answers (1)

Nybbe
Nybbe

Reputation: 404

You could solve it by having Vlookup-formulas in your Master sheet. That way, if you change anything in sheet 2 and 3 the Master will automatically be updated. If the user changes anything in the Master sheet, you will have to build logic in VBA on that. One way to go is to format the Master sheet so that there is something that helps the VBA know what the formula should be in the edited cell, and also to know from where the data should come. Loosely one could set up the Master sheet like this:

  • Row 1 is hidden and contains the template formulas
  • Row 2 is hidden and is completely empty (this will make less problems with filtering)
  • Row 3 contains headers
  • Row 4 and down contains the data, using the formulas define in row 1
  • Add the Change event on the Master sheet, that sees if the changed cell was one with a formula. If so, it will examine the template formula to identify from where the data should come. Then it will update that cell in Sheet 2 or 3 with the new value that is entered in the Master sheet. After this, it will overwrite the value manually entered in the Master sheet with the formula from the template row.

The big job here is to write a parser that understands from which cell the vlookup will get it's value.

One thing that I overlooked is that the CHANGE event is triggered only ONCE if the user pastes several cells in one go. The TARGET will then contain several rows or columns.

So this is some kind of skeleton using the above idea...

Option Explicit

Dim ChangeEventDisabled  As Boolean           'Flag for disabling the Change event

Public Sub Disable_ChangeEvent()
    ChangeEventDisabled = True
End Sub
Public Sub Enable_ChangeEvent()
    ChangeEventDisabled = False
End Sub


Sub Worksheet_Change(ByVal Target As Range)
Dim updatedValue As Variant
Dim SourceCell As Range


    'While the MasterSHeet is populated intially, we don't want this event to do anything
    If ChangeEventDisabled Then
        'There are chenges being done in teh sheet that should not trigger updates of the source-sheets.
    Else
        'Only run the code if it was a data-cell that was changed
        If Target.Row > 3 Then
            'We are in the rows containg data
            'Did the changed cell contain a Vlookup formula before the user changed the cells value?
            If UCase(Cells(1, Target.Column).Formula) Like "=VLOOKUP(*" Then
                'A vlookup normally populates this cell.
                'To know from where the data normally comes, I will need to put back the formula in the changed cell.
                'So, first save the new value that we will write in the source cell
                updatedValue = Target.Value
                'Insert the formula again in the cell
                'As we will now CHANGE a cell in the Masterr sheet, a Change event will trigger. Disable it temporarily
                Disable_ChangeEvent
                Cells(1, Target.Column).Copy Destination:=Target
                Enable_ChangeEvent
                'Find out from which cell the data is being fetched by the Vlookup
                Set SourceCell = MyMagicParsing(Target)
                'Update the source-cell with the new value
                SourceCell.Value = updatedValue
            End If
        End If
    End If

End Sub


Function GetSourceCell(Target As Range) As Range
'This function should decipher the formula in the cell Target, and figure out from where
'the data is actually coming. It shoudl return the range which is the source of the data.
'As I dont know how to do that quickly, I just hardcode the cell that is the source.
    GetSourceCell = Worksheets("Sheet2").Cells(67, 3)
End Function

Upvotes: 1

Related Questions