Is there such a think as "dual link"?

K

ksks16

I am trying to link 2 worksheets to each other. By copying cell A1 i
Sheet 2 and pasting it as a link to cell A1 in Sheet 1 - I can mak
changes to my data ONLY by updating it in Sheet 2. If I update dat
in Sheet 1 it simply replaces the created link with my typed data.
Data is text.


Is there a way to "connect" these two cells so I can update data i
EITHER place and it will automatically update the other - without bein
forced to always return to my original Sheet 2?

HELP!!!
-ksks1
 
K

Ken Wright

Not without using VBA, ie macro code. You would have to have a change event
on each sheet that interrogated any change on the sheet to see if it was the
cell in question, and if indeed it was then it would write the same value
into the corresponding cell on the other sheet. This cannot be done with
formulas.

If you are unable to run macros in your work environment then you would be
unable to go this route.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
K

ksks16

Is the help menu clear about macros? I've never done one before and
would need a step-by-step instruction. Any suggestions where I could
go for some good directions?

Thanks for the response!!

ksks16
 
K

Ken Wright

Lets assume that your first sheet is named Sheet1 and your second sheet is
named Sheet2

Right click on the tab of Sheet1 and choose 'view code'

Paste the following into the white space that you can now see:-


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False

Set wks1 = ActiveSheet
Set wks2 = Worksheets("sheet2")

wks2.Range("A1").Value = wks1.Range("A1").Value

Application.EnableEvents = True
End If
End Sub


Now hit File / Close and return to Microsoft Excel, then right click on the
tab of Sheet2 and paste in the following:-


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False

Set wks1 = ActiveSheet
Set wks2 = Worksheets("sheet1")

wks2.Range("A1").Value = wks1.Range("A1").Value

Application.EnableEvents = True
End If
End Sub

Now hit File / Close and return to Microsoft Excel and you are done.

If your sheet names are differenet then just change the ones listed above
for your actual sheet names.

Regards
Ken....................
 
K

ksks16

I've tried to do the copy & paste of the VB code just as described.
(BTW>> Thank you so much for putting that together!!!)

Unfortunately, I cannot get it to work. I even had a co-worker try in
case it was a "user error" on my part.

Any other suggestions?

ksks16
 
K

ksks16

OK. I'm really working hard on this. My problem is that I want
everything on sheets 2,3,4 etc. to be mirrored on sheet 1 and still be
able to edit in either place. I don't want the contents of sheets 2-4
to replace themselves.

In my project, I have sheets 2 - 4 with yearly data (sheet2=2005;
sheet3=2004; sheet4=2003; etc) and sheet1 is my master list where I
would like to show ALL of the years combined. If I see an error on
sheet4 I want to be able to update it and the change transfer to
sheet1; or if an error is on sheet1 - update it and the change transfer
to the appropriate sheet.

Sheet1 needs to be able to be sorted in different ways without losing
the information and also needs to have the capability of adding a 5th
or 6th sheet.

Complicated, I know. If there is ANY way to do this, a suggestion
would be great. Otherwise I can just keep updating the individual
sheets to their respective link on Sheet1.

ksks16
 
K

Ken Wright

Wanting to be able to sort will make this a LOT harder than just having
static cells writing to each other.

As far as the macro bit goes, I'd be happy to send you a dummy workbook if
you like, though with your latest twist, I no longer think it will do what
you need.

If I might make a suggestion though, have you considered having all your
data on a single master sheet as described, and then using that as the
source for a Pivot table, which would allow you to possibly create
individual years on other sheets if thats what you want. That way you only
worry about updating in one place.

Also, depending on your data, it might also be possible to do this with
formulas, linking the individual sheets into the master sheet. Personally
though I'd be trying to make it work via a Pivot table if it could.

It sounds like your data is in a databse style format anyway, so if that is
the case then you have an ideal base for a Pivot table.

Regards
Ken......................
 
Top