Linking cells in Excel

[segmentationfault]

I apologize in advance if this is a somewhat stupid question. Everything I do with Excel is done through VBA, as a result, I'm really not knowledgeable about what Excel can do on its own.

I am working with two worksheets. The second sheet is an expansion of the data on the first sheet. For the data that is simultaneous on both sheets, it needs to be editable on both sheets and it needs to always be synchronized. Hm, if only worksheets could share cells as reference variables... Anyhow, it occurred to me that Excel may have the ability to do perform this task, but every synonym for "link" for which I searched in the help index returned me to hyperlinks.

I have a pretty swift macro that accomplishes the task well, but I'm creating this file for use by someone else. I don't want to get a phone call about fixing the macro if he manages to screw it up sometime in the future. Is there a better way to achieve my goal? Am I needlessy sweating a simple task?

 

[SkipVought]

Hi,

If I am understanding you, then here is a way WITHOUT link formulae, using VBA...

1. In the Sheet1 object Woksheet_Change event, call...

Private Sub Worksheet_Change(ByVal Target As Range)
    SynchronizeSheets Target
End Sub

2. In a module, code SynchronizeSheets

Sub SynchronizeSheets(ByVal Target As Range)
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet2")
    For Each t In Target
        With t
            wks.Cells(.Row, .Column).Value = .Value
        End With
    Next
End Sub

This will replicate each entry from Sheet1 in Sheet2. I don't suppose that that is exactly what you want, but the approch can be made to work.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[ratman]

I use links to a closed workbook like this......don't know if this helps.

='C:\[PalflexAutomotivePriorityTable.xls]Sheet1'!A4

 

[segmentationfault]

That's how my macro started, SkipV, but for large ranges, the recursive calls make everything go a little crazy. A semaphore helps. In addition, I deconstruct and reconstruct the range addresses so that I don't synchronize parts of the sheets that shouldn't be (ie where one sheet has the expanded data and the other sheet has blank space or notes).

I didn't try linking to a closed workbook, but I did try linking to cells on the other worksheet. That would work if it was read-only, but we need to be able to edit on both sheets without destroying the link.

I read this forum a lot, and if neither of you know of a builtin way to make Excel do this, then my question is satisfied. I'll just keep refining my macro and hope I don't get angry phone calls from conniving users in the future.

 

[segmentationfault]

Er, forgot to add, thanks for the quick responses. Even if the answer is No, it helps to be sure sometimes.

 

[segmentationfault]

Er, forgot to add thanks for the quick responses. Even if the answer is No, it helps to be sure sometimes.

 

[SkipVought]

Hey, nofaultofmind,

Send me a sample of your workbook VBA et al, and I'll look at it Skip,
SkipAndMary1017@mindspring.com