MS Excel 2003: Link one cell to another cell (control the recalculation)
This Excel tutorial explains how to write a macro to link one cell to another and control the recalculation in Excel 2003 and older versions (with screenshots and step-by-step instructions).
Question: In Microsoft Excel 2003/XP/2000/97, is there a way to link a cell in one column to a cell in another? For example, when I enter a memo number in column A, I need a date value to appear in column B (in the corresponding row).
Once the date value has been set in column A, I do not want the value to change. (ie: The date value should not change the next day when the file is opened.)
Answer: This can be accomplished using a macro.
Let's look at an example.
Download Excel spreadsheet (as demonstrated below)
On the sheet called Sheet1, we've placed code on the "Worksheet_Change" event, so that whenever the data in column A changes, the associated cell in column B will be updated.
You can press Alt+F11 to view the VBA code.
Note: The dates will re-calculate if you, for example, "insert a column before column A and then delete it". So make sure that your column structures are static before you rely on the linked date values.
Macro Code
The macro code looks like this:
Private Sub Worksheet_Change(ByVal Target As Range) Dim LLoop As Integer Dim LTargetRange1 As String Dim LDestRange1 As String LLoop = 2 'Check first 200 rows in spreadsheet While LLoop <= 200 'Link column A to B LTargetRange1 = "A" & CStr(LLoop) LDestRange1 = "B" & CStr(LLoop) If Not Intersect(Range(LTargetRange1), Target) Is Nothing Then If Len(Range(LTargetRange1).Value) > 0 Then Range(LDestRange1).Value = Date Else Range(LDestRange1).Value = Null End If End If LLoop = LLoop + 1 Wend End Sub
Advertisements