How to create two-way updating tranposed linked sheets

**osamaosamosama** · 04-23-2013, 10:45 AM

Dear excel guru's!

I want to be able to enter data into as a user in either the original sheet (SheetA) OR in a transposed version of precisely the same data (SheetB).
That is, entering data in SheetA updates the values in the transposed version of itself (SheetB), and entry of values in SheetB should also update values in SheetA.

Is this too complicated a request? Thank you for your time.

osama

**osamaosamosama** · 04-23-2013, 10:46 AM

test 1234567890

**TMS** · 04-23-2013, 11:04 AM

Try this in the Workbook Class module:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Select Case ActiveSheet.Name
Case Is = "Sheet1"
    Sheets("Sheet2").Cells(Target.Column, Target.Row).Value = Target.Value
Case Is = "Sheet2"
    Sheets("Sheet1").Cells(Target.Column, Target.Row).Value = Target.Value
Case Else
End Select
Application.EnableEvents = True
On Error GoTo 0
End Sub

Regards, TMS

**TMS** · 04-23-2013, 11:27 AM

Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**osamaosamosama** · 04-23-2013, 11:28 AM

Thank you TMS, can you please recommend me an instructional reference as how to implement your suggestion? Unfortunately I am too much a novice to implement your script! I added a class module, copy pasted your script, then tested whether a change in Sheet1 transfers a transposed change to Sheet2, but alas to no avail (as yet).

**TMS** · 04-23-2013, 12:07 PM

In your workbook, press Alt-F11 to open the VB Editor, or you can right click on any sheet and choose "View Code".

In the panel on the left, double click on the ThisWorkbook component. This will open up a pane for the Workbook Class module.

Copy and Paste the code there, exactly as it is in the thread.

Save the workbook (optional) and test inputting data into Sheet1 and Sheet2.

Regards, TMS