Creating two "interdependent, linked" cells on a spreadsheet

**boom929** · 06-21-2010, 12:39 PM

I'm not sure if I've used the right terms but here is what I'd like to do:

I have a spreadsheet (attached) and column B is for "account #" while column C is for "distributor". If possible, I would like for cell C2 to be populated (see list tab) with the appropriate distributor name based on what acct # is selected in B2.

And vice-versa; I would like cell B2 to be populated with the respective acct # when a specific distributor is selected.

Is there a way to make these two cells dependent on each other like this? Is what I'm asking clear enough?

Thanks,

BR

**tony h** · 06-21-2010, 06:29 PM

I would use the worksheet_change event to achieve this.

**ashwin.khadgi** · 03-05-2014, 08:17 AM

Originally Posted by tony h

I would use the worksheet_change event to achieve this.

But how?

**tony h** · 03-05-2014, 04:33 PM

This sample will help to show you how. Paste this code into the worksheet change event then type into cells A2 or B2 for that sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Target.Address
    Case "$A$2"
        ActiveSheet.Range("$B$2") = "B is now " & ActiveSheet.Range("$A$2")
    Case "$B$2"
        ActiveSheet.Range("$A$2") = "A results " & ActiveSheet.Range("$B$2")
    End Select

    Application.EnableEvents = True
End Sub