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

[willow1985]

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!

 

[Skyybot]

Are they formatted as Tables, or as Range? What Sheet(s) are they on?

 

[KillerOfGiants]

If they are identical in every way, and the hidden table updates based on any input from the visible table; what, then, is the objective? Why have identical tables in every way in the same workbook that can are modified by the same entry?

 

[HighAndWilder]

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!

When will TableHidden be seen / used?

If the two tables are identical then why not just replicate TableSee to create TableHidden as and when TableHidden needs to be seen / used instead of updating it
when changes are made to TableSee?

 

[willow1985]

They are formatted tables. The purpose is the hidden table is on a sheet with other information and tables that cannot be seen/used and is referenced by a Macro. I need them linked so the user can only update that table but they do not have access to the hidden sheet.

Is it possible to link two tables in the same workbook so that when I add an entry to one it updates the other? Regardless if it makes sense, this is what has been asked of me. If anyone can help it would be greatly appreciated.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your TableSee sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the sheet name (in red) to match the hidden sheet's name. Close the code window to return to your sheet. Make a change in TableSee and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("TableHidden").Range(Target.Address(0, 0)) = Target
End Sub

 

[willow1985]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your TableSee sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the sheet name (in red) to match the hidden sheet's name. Close the code window to return to your sheet. Make a change in TableSee and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("TableHidden").Range(Target.Address(0, 0)) = Target
End Sub

How would I modify this if the table on the Hidden sheet starts on H1 and the one that is seen is A1?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("TableHidden").Range(Target.Address(0, 0)).Offset(, 7) = Target
End Sub

 

[willow1985]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("TableHidden").Range(Target.Address(0, 0)).Offset(, 7) = Target
End Sub

This works but if I delete a row in See Table I get a runtime error and it does not reflect on the Hidden table. Is there a way to fix this?

 

[mumps]

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?