Cross Linking cells in 2 worksheets.

Good afternoon,

I am trying to get my hands on cross-linking cells in column Q with 2 other sheets, but in the same workbook, to change each other through a macro.

Like this code that only works on the same sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell1 As Range
Dim rCell2 As Range
If Target.Count > 1 Then Exit Sub
Set rCell1 = Range("A1")
Set rCell2 = Range("A2")
Set rCell3 = Range("A3")
Application.EnableEvents = False
Select Case Target.Address
Case rCell1.Address
rCell2.Value = rCell1.Value
rCell3.Value = rCell1.Value
Case rCell2.Address
rCell1.Value = rCell2.Value
rCell3.Value = rCell2.Value
Case rCell3.Address
rCell1.Value = rCell3.Value
rCell2.Value = rCell3.Value
End Select
Application.EnableEvents = True
End Sub

In this code you can place a value in any of the 3 cells, and the other 2 will change suit.
I need this to happen between 3 sheets.

I have 4 sheets, 1st sheet is the main sheet. Where I will put an Item delivered, pending, or dead in column Q.
The 2nd Sheet will have all pending deals, and the 3rd sheet will have all dead deals. They will most likely be on there by vlookup function.
Sheet 4 has all my drop down list, so its irrelevant right now.

What I need the spreadsheet to do, is put "pending" or "dead" in column Q for sheet1, I want that row to hide. And that hidden row to copy itself to it's corresponding sheet.
Sounds Easy right. Thats as far as I can go.

The hard part is when the "pending" or "dead" deal become "delivered"

I want to go to the pending sheet and change "Pending" on column Q, to "Delivered". Which is linked to the 1st sheet. The 1st sheet will unhide itself and will remove itself from the pending section.

And then do the same in the dead sheet. (unlikely, but just in case)

That is the hard part.

Any help in the right direction would be greatly appreciated.


Regards,
UnSpoknOne

Originally Posted by watersev

hi, UnSpoknOne, any chance to see sample workbook?

Here you go...New_Deliveries_demo.xls

Thanks in advance.

[BUMP] still need help here.

Originally Posted by watersev

sorry for late reply, try this if it's sort of what you need

No, thank you so much for helping, I did not know if you were still helping me. I get an error in your code.

I've decided to let the 3 pages be exactly the same. (To make it easier for me to understand. I will need to edit it in the future.)
First page is the main page where I put all of the info. It will auto hide every row but "Delivered" in column Q.

Second Page is an exact replica where i will make for example A5 on page to =Sheet1!A5, and that page hides everything but "Pending" in column Q.

Third Page same as 1st and 2nd page but hides everything but "dead" in column Q.

What I need is a way for all 3 pages to be controlled by column Q on all pages.

This should make it easier than just working with 135 rows on page one and 40 on the others.

New_Deliveries_demo1.xls

Here is the new file to work with, I need the other macro that I have there as well. I print the first page and I need the nonused rows to stay hidden.

Thanks in advance watersev,

UnSpoknOne