Excel 2007 : 2 way data within 1 workbook

**brett701** · 06-20-2011, 07:15 PM

I have 1 workbook.
10 sheets in this workbook.
i created another sheet titled EVERYTHING.
In the EVERYTHING worksheet i have performed a copy/paste special link so that if i change something in the individual worksheets it will change on the EVERYTHING worksheet;however, i don't know how to make it work in the other direction.
Id like to put entries in the EVERYTHING worksheet and it will change the individual worksheets.
Also the EVERYTHING worksheet is a table(i dont know if that matters).

Thanks for your help everyone.

**jeffreybrown** · 06-20-2011, 09:44 PM

Hi brett701 & Welcome to the Board,

My first thought is why? Doesn't appear to be an appropriate way to run a worksheet. I don't see a way you could do this with a formula, you would need VBA code.

If you wanted to go with the VBA you could try this, but it needs to be modified to meet your setup.

In the example below, you would enter the word "Pending" in column 10 (J) and it would transfer to the Pending tab. If Assigned was entered then to sheet Assigned.

Why not just use the main sheet a run some filters to view different sets of data?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 10 Then Exit Sub
    Select Case Target
    Case "Pending"
        Target.EntireRow.Copy _
        Sheets("Pending").Range("A" & Sheets("Pending").Cells(Rows.Count, 1).End(xlUp).Row + 1)
    Case "Assigned"
        Target.EntireRow.Copy _
        Sheets("Assigned").Range("A" & Sheets("Assigned").Cells(Rows.Count, 1).End(xlUp).Row + 1)
        'etc
    End Select
End Sub

With whatever you do it could help if you post a sample workbook with what you have and what you want.