Is there such a think as "dual link"?

[ksks16]

I am trying to link 2 worksheets to each other. By copying cell A1 in Sheet 2 and pasting it as a link to cell A1 in Sheet 1 - I can make changes to my data ONLY by updating it in Sheet 2. If I update data 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 in EITHER place and it will automatically update the other - without being forced to always return to my original Sheet 2?

HELP!!!
-ksks16

[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 :-)
------------------------------*------------------------------*----------------



"ksks16" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to link 2 worksheets to each other. By copying cell A1 in
> Sheet 2 and pasting it as a link to cell A1 in Sheet 1 - I can make
> changes to my data ONLY by updating it in Sheet 2. If I update data
> 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 in
> EITHER place and it will automatically update the other - without being
> forced to always return to my original Sheet 2?
>
> HELP!!!
> -ksks16
>
>
> --
> ksks16
> ------------------------------------------------------------------------
> ksks16's Profile:
> http://www.excelforum.com/member.php...o&userid=30841
> View this thread: http://www.excelforum.com/showthread...hreadid=505045
>

[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

[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....................



"ksks16" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> ksks16
> ------------------------------------------------------------------------
> ksks16's Profile:
> http://www.excelforum.com/member.php...o&userid=30841
> View this thread: http://www.excelforum.com/showthread...hreadid=505045
>

[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

[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

[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......................

"ksks16" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> ksks16
> ------------------------------------------------------------------------
> ksks16's Profile:
> http://www.excelforum.com/member.php...o&userid=30841
> View this thread: http://www.excelforum.com/showthread...hreadid=505045
>