Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there such a think as "dual link"?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there such a think as "dual link"?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there such a think as "dual link"?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there such a think as "dual link"?
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there such a think as "dual link"?
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 ------------------------------------------------------------------------ ksks16's Profile: http://www.excelforum.com/member.php...o&userid=30841 View this thread: http://www.excelforum.com/showthread...hreadid=505045 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there such a think as "dual link"?
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there such a think as "dual link"?
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|