Excel – How to require a cell in one column to have a value if a cell in another column (same row) has a value

data validationmicrosoft excelmicrosoft-excel-2010

I'm trying to require a value to be populated in column A if column D has an amount in it. Column A can hold any type of value (text or numerical), column D can only be a number.

So, for example, if cell D1 is 11, cell A1 would require a value, and ideally have a popup message alerting the user to the problem.

I've looked into the Data Validation function, but it seems to only validate a cell based on that cell's value. If it's possible, I haven't been able to figure out how to make Data Validation validate one cell based on the value of another.

Any ideas?

Editing to add – I'm trying to do this because I have a column of dollar amounts (column D). Column A should be the invoice number associated with the dollar amount in Column D. Each amount in column D must have an invoice number in column A. I already have conditional formatting in the spreadsheet, and apparently bright red cell coloring isn't a good enough indicator of "you did something wrong" for the individuals filling out the spreadsheet.

Best Answer

You can start with this in the VBA of the worksheet, adjust accordingly

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Column <> 1 Then
        Exit Sub
    End If

    ActiveSheet.Unprotect


    If IsEmpty(Target) Then
       Target.Offset(, 1).Clear
       Target.Offset(, 1).Locked = True
    Else
        Target.Offset(, 1).Locked = False
    End If


    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
Related Question