I know I can change the structure of the program... I find it more
flexible if I can update in the both way... As you said it is like
playing with *fire*.
As I'm a newbie at Excel... I dont' know whether it is impossible or
not.
I would like to know, can it be done? I know it is very much simple to
make a mirror of *two* sheets and both way updatable.
Ain't there any ways to link two sheets and can be updated from the
both ends? Can't it be done using *Circular Reference*?
Harlan Grove Wrote:
munim wrote...
....
My problem ain't that complex... All I want is a mirror copy of a
sheet
with a different outlook... :( :(
....
Not so. Reread your own words . . .
"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..."
You don't understand that what you describe *IS* complex. Perhaps
that's why you can't figure out how to do it yourself. A true *MIRROR*
copy is pretty simple - all cells in one worksheet are *exactly* like
the same cell in the mirror worksheet. However, what you want is 2-way
links between different cells in different worksheets.
At a minimum, you'd need a 2 column table with Sheet1 references in
the
1st column and corresponding Sheet2 references in the 2nd column. So
given your example above,
Sheet1!A1 Sheet2!B5
Then you'd need a SheetChange event handler in the workbook's
ThisWorkbook class module that checks whether changed cells appear in
the table, and if so makes an exact copy of the changed cells in the
corresponding cells. As long as all you're entering are constants and
formulas without any relative references, not too difficult. But what
should happen if the user enters
=X99
in Sheet1!A1? Should the Sheet2!B5 formula be
=Sheet1!X99
=Sheet1!Y103
=X99
=Y103
or
=Sheet1!A1
?
For that matter, what should happen if the user enters =Sheet2!B5 as
the formula in Sheet1!A1?
It's a very basic tenet of software development that multiple entry of
the same information is INVARIABLY a *BAD* thing. Redesign your
application so that one or the other of your 'mirror' worksheets is
the
sole entry worksheet or allow entry in *neither* but instead use other
worksheets or user forms for *common* entry.
You could use some trickery to accomplish this. Cells Sheet1!A1 and
Sheet2!B5 could both reference Sheet99!X99, and you could use a
SheetChange event handler to check if the changed cell is either
Sheet1!A1 or Sheet2!B5, and if so store the entered value in a VBA
variable, back out the entry in the cell in which is was made using
Application.Undo, then put the stored entry into Sheet99!X99. You'd
still have the specification questions above to resolve, but you'd
have
a single common place for data storage even though you could make
entries from multiple places.
IMO, this form of trickery is more trouble than it's worth, but I also
believe that experience is the best teacher, and you seem to need
instruction about not playing with fire.
--
munim
------------------------------------------------------------------------
munim's Profile:
http://www.excelforum.com/member.php...o&userid=30052
View this thread:
http://www.excelforum.com/showthread...hreadid=497307