A Mirror Copy of a Sheet with Different Outlook
 

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

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 ,
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

A Mirror Copy of a Sheet with Different Outlook
 

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


--
muni
-----------------------------------------------------------------------
munim's Profile: http://www.excelforum.com/member.php...fo&userid=3005
View this thread: http://www.excelforum.com/showthread.php?threadid=49785

A Mirror Copy of a Sheet with Different Outlook
 

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 ,
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