asked on

MS Excel - automatically update a cell based on whether another cell is populated; otherwise leave it alone

I have a cell N30 which is normally blank but which can contain a date. I have cell E30 which almost always contains a user-entered date. I need something that will automatically overwrite the contents of cell E30 if cell N30 contains a value, but which otherwise leaves the contents of E30 alone. I'd like the workbook to do this automatically. And I'd like the functionality to extend over a range -- e.g., if any cell in column N is populated, it updates its equivalent cell in column E but otherwise leaves the cells alone.

I'd like to stay away from circular references if possible. It's also not necessary to retain the previous value of E anywhere -- once it's overwritten, it's overwritten.

I'm pretty good at VBA but the way Excel refers to locations has me stumped (among some other things). So.....help...! :)

Patrick Matthews

In your VBA editor, open the sheet module for the worksheet you want to apply this to, and paste this in:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    
    Set rng = Me.Range("n:n")
    
    If Not Intersect(Target, rng) Is Nothing Then
        Me.Range("e30").ClearContents
    End If
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alfamikefoxtrot

ASKER

Hi Patrick -- thanks for the help so far. A couple of things -- it looks like cell N30 continues to stay selected after the operation completes. Also -- and I probably didn't make the wording as straightforward as I could have -- I'm looking to update the corresponding cell in col E if the cell in col N is not blank (so if N21 is populated, E21 would overwrite). Last -- I neglected to mention that the N range is actually being fed by another function -- might be a complicating factor. I've appended a redacted sheet for you to play with -- feels like we're pretty close, just needs a little tweaking. Thanks again for the help...!
pasteout2.xlsm

alfamikefoxtrot

ASKER

Still need some help with this one -- see attached XLS. Any takers?

alfamikefoxtrot

ASKER

Thanks for taking a stab at it. I ended up resolving without VBA.