A Mirror Copy of a Sheet with Different Outlook

Interesting idea. The foll. is lightly tested. Also note that
typically I would not use a workbook event procedure but would
implement an application event procedure. However, for demonstration
purposes, this is much easier.

Suppose your two worksheets are named View1 and View2. Create a new
worksheet named Map. This will contain a map of what cell in View1
corresponds to what cell in View2. In my tests, I had:
Item View1 View2
Acct ID C4 B5
Name G4 B4

Then, in the VBE, in the ThisWorkbook module put the following code:
Option Explicit

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Range)
Dim Update_Addr As String, UpdateCell As Range, _
SearchCol As String, TargCol As String, TargWS As String
If Sh.Name = "View1" Then
SearchCol = "B:B": TargCol = "C:C": TargWS = "View2"
ElseIf Sh.Name = "View2" Then
SearchCol = "C:C": TargCol = "B:B": TargWS = "View1"
End If
On Error Resume Next
Update_Addr = Application.WorksheetFunction.Index( _
Sh.Parent.Worksheets("map").Range(TargCol), _
Application.WorksheetFunction.Match( _
Target.Address(False, False), _
Sh.Parent.Worksheets("map").Range(SearchCol), _
0))
Set UpdateCell = Sh.Parent.Worksheets(TargWS).Range(Update_Addr)
Application.EnableEvents = False
UpdateCell.Value = Target.Value
Application.EnableEvents = True
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> Hello Group!
>
> Let me describe my problem briefly...
>
> For Instance, I select a cell sheet1!A1 and another sheet2!B5... If I
> put anything in sheet2!B5, it will automatically update sheet1!A1 and
> if I put sheet1!A1 then I will update sheet2!B5...
> _
> Theme:_
>
> I've a sheet and it has an outlook all the calculations are done
> there... and I have another sheet which has almost the same values with
> a different outlook.
>
> So if I update any values of any of the sheet it will update the other
> sheet. Though It is has total different look. And the links in sheet1
> and sheet2 are random... i.e. sheet1!A5 -> sheet2!B3; sheet1!C6 ->
> sheet2!A10 goes on...
>
> The main thing is, all I want is
> *_A_Mirror_Copy_of_a_Sheet_with_Different_Outlook_*
>
> THANKS IN ADVANCE
>
>
> --
> munim
> ------------------------------------------------------------------------
> munim's Profile: http://www.excelforum.com/member.php...o&userid=30052
> View this thread: http://www.excelforum.com/showthread...hreadid=497852
>
>

How and where would I put the values in the map sheet?

Originally Posted by Tushar Mehta

Suppose your two worksheets are named View1 and View2. Create a new
worksheet named Map. This will contain a map of what cell in View1
corresponds to what cell in View2. In my tests, I had:
Item View1 View2
Acct ID C4 B5
Name G4 B4

Columns A:C (View1 is in B). The headings are in row 1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> How and where would I put the values in the map sheet?
>
> Tushar Mehta Wrote:
> > Suppose your two worksheets are named View1 and View2. Create a new
> > worksheet named Map. This will contain a map of what cell in View1
> > corresponds to what cell in View2. In my tests, I had:
> > Item View1 View2
> > Acct ID C4 B5
> > Name G4 B4
> >
>
>
> --
> munim
> ------------------------------------------------------------------------
> munim's Profile: http://www.excelforum.com/member.php...o&userid=30052
> View this thread: http://www.excelforum.com/showthread...hreadid=497852
>
>