Restrict cell input based on another cell value

Hi everyone, this is my first post here. I consider myself a more advanced Excel user and can navigate my way around fairly well. I've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.

In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.

Can anyone help with this, because I can't figure it out....

Yes, this can be done with a Data Validation trick.

Let's say it's B2:B10 and you want to make sure the value in the adjacent cell in column A is there before entry is allowed.

Select B2:B10
Data > Data Validation
Allow: Custom
Formula: =AND(A2<>0,A2<>"")
OK

Welcome to the forum, Falk.

Please take few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

Change the initial DV formula to:

=AND(A2<>0,A2<>"",ISNUMBER(B2))

Perhaps a macro that deletes the contents in B2 when A2 is deleted?

A macro button would be used to create a macro you want to manually activate. That's not what you want, is it? You want this to happen on its own.

Right-click on the sheet-tab where you want this to occur and select VIEW CODE. Paste in this code and edit the range I highlighted to cover the cells you want watched:

Please Login or Register  to view this content.

The "Offset" means 1 column to the right, or column B. Adjust the offset, too, to target your cell to clear properly.

I tried what you said, but I can't get it to work. It's not having any affect on anything. I'm clearly doing something wrong. I do want it to happen automatically. I don't understand the offset at all, it's not clear to me. Is there any other way to explain it?

What are the two columns you need this to work in? Which has the data validation?

Also, no need to use the QUOTE button, I already know what I said. Just use the Quick Reply.

I wanted to PM the spreadsheet to you, but apparently you can't do it.

The concern is in C2 and C9. Enter the number of employees in C2 and the number of "near misses" in C9. Once this is done, delete out the number of employees and the near misses still stay. I need the near misses to go away when the employees are deleted.

Is there a way to send you the spreadsheet?

This code will watch cell C2 and clear the contents of C9 if C2 is cleared.

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2")) Is Nothing Then _
           If Target.Value = "" Then Range("C9").ClearContents
End Sub 


That should work for the two specific cells you asked for. If you've oversimplified your need, then restate the broader need, or click on GO ADVANCED and use the paperclip icon to post a sample workbook.

Care to post up your final code?

Your code clears ALL the cells noted in row 9 if even 1 cell is cleared in row 2. Use this instead to only clear the cell in the same column.

Please Login or Register  to view this content.

Or this version which will clear multiple cells if you clear a bunch or row2 cells all at once.

Please Login or Register  to view this content.

sureshpa,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

See post #21.