Linking 2 identical tables in the same workbook so when one updates the other updates

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
931
Office Version
  1. 365
Platform
  1. Windows
I am not skilled in power query or power pivot and am wondering if someone could advise how I can link 2 identical tables in the same workbook?
One table is on a hidden sheet and the other is for anyone to see.
The visible one we will call TableSee and the Hidden one we can call TableHidden.

I would like it when you add data to TableSee it automatically updates TableHidden. They only have 2 columns and are identical in every way.

Help on this would be greatly appreciated. I have tried links and other information online in power query but I could never get either table to update when new information was added.

Thank you to anyone who can help!
 
Is the data in the two sheets limited to columns A and B in See Table and H and I in the Hidden table or do the sheets contain data in other columns?
On "See" there is only the table in column A and B, and in "Hidden" there is information in other columns but the table only exists in Column H and I
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hello! If you say that both tables are identical, then you can create a request to Power Query on the first (visible) and, based on it, build a second table (hidden). After that, in the request settings, install the update interval.
 
Upvote 0
Hello! If you say that both tables are identical, then you can create a request to Power Query on the first (visible) and, based on it, build a second table (hidden). After that, in the request settings, install the update interval.
I am not very skilled with Power Query and do not know how to do this.
 
Upvote 0
Replace the current code with this:
VBA Code:
Dim str As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        str = Target.Cells(1).Address(0, 0)
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        Sheets("TableHidden").Range(str).Offset(, 7).Resize(, 2).Delete shift:=xlUp
    Else
        Sheets("TableHidden").Range(Target.Address(0, 0)).Offset(, 7) = Target
    End If
End Sub
 
Upvote 0
Solution
Replace the current code with this:
VBA Code:
Dim str As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        str = Target.Cells(1).Address(0, 0)
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        Sheets("TableHidden").Range(str).Offset(, 7).Resize(, 2).Delete shift:=xlUp
    Else
        Sheets("TableHidden").Range(Target.Address(0, 0)).Offset(, 7) = Target
    End If
End Sub
I am getting a Compile error: Argument not optional on line str = Target.Cells(1).Address(0,0)

Quality Log.xlsm
AB
1EmployeeEmail
2Emp1Emp1@email.com
3Emp2Emp2@email.com
4Emp3Emp3@email.com
5Emp4Emp4@email.com
6Emp5Emp5@email.com
7Emp6Emp6@email.com
8Emp7Emp7@email.com
9Emp8Emp8@email.com
10Emp9Emp9@email.com
11Emp10Emp10@email.com
12Emp11Emp11@email.com
13Emp12Emp12@email.com
14Emp13Emp13@email.com
15Emp14Emp14@email.com
16Emp15Emp15@email.com
Employee List

Quality Log.xlsm
ABCDEFGHI
1Confidential info1Confidential info13Confidential info25Confidential info30EmployeeEmail
2Confidential info2Confidential info14Confidential info26Confidential info31Emp1Emp1@email.com
3Confidential info3Confidential info15Confidential info27Confidential info32Emp2Emp2@email.com
4Confidential info4Confidential info16Confidential info28Emp3Emp3@email.com
5Confidential info5Confidential info17Confidential info29Emp4Emp4@email.com
6Confidential info6Confidential info18Confidential info30Emp5Emp5@email.com
7Confidential info7Confidential info19Emp6Emp6@email.com
8Confidential info8Confidential info20Emp7Emp7@email.com
9Confidential info9Confidential info21Emp8Emp8@email.com
10Confidential info10Confidential info22Emp9Emp9@email.com
11Confidential info11Confidential info23Emp10Emp10@email.com
12Confidential info12Confidential info24Emp11Emp11@email.com
13Confidential info13Confidential info25Emp12Emp12@email.com
14Emp13Emp13@email.com
15Emp14Emp14@email.com
16Emp15Emp15@email.com
Hidden


1732050592668.png
 
Upvote 0

Forum statistics

Threads
1,225,726
Messages
6,186,677
Members
453,368
Latest member
xxtanka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top