0

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.

4
  • 1
    I don't understand. Why are you needing to do this? Jan 16, 2014 at 10:47
  • 1
    Conditional formatting would be the way to go IMHO. Far less intrusive/annoying than an alert box and much easier to see where the problem is.
    – Andi Mohr
    Jan 16, 2014 at 11:23
  • If that's your reasoning, why not lock the dollar cells and only have them unlock when an invoice is entered? This could be done in VBA. And if the invoice number is deleted, it clears the dollars? Jan 16, 2014 at 15:14
  • 1
    @Raystafarian Hadn't thought about handling it like that, but the VBA you posted below does exactly what I needed. Great idea!
    – Jaime
    Jan 16, 2014 at 16:47

2 Answers 2

0

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
1

You can use the Custom option in the Data Validation dialog (Settings tab). Select the data in column D and click Data Validation. Choose Custom in the Allow dropdown, and enter the formula:

=OR(ISBLANK(A1),NOT(ISBLANK(D1)))

Make sure Ignore blank is unchecked as well.

Unfortunately, having a blank value (e.g. typing a value and then deleting it) doesn't seem to trigger the popup. You can verify that it regards the value as being invalid by selecting the Circle Invalid Data button, but it won't ever give you a popup messsage. (I hate those popups anyway!) You could always put that formula in another column, perhaps in the form

=IF(OR(ISBLANK(A1),NOT(ISBLANK(D1))),"","Enter a value")

Or you could use Conditional Formatting to highlight invalid cells.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .