Closed Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 23

Restrict cell input based on another cell value

  1. #1
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    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....
    Last edited by Falk781; 07-30-2009 at 04:15 PM.

  2. #2
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Here's a question.....

    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
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,554

    Re: Here's a question.....

    Welcome to the forum, Falk.

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

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Here's a question.....

    My first day on the job and I'm in trouble already. I'm sorry for that, I was just in a hurry for some help. I'll be sure and read the requirements before posting again.

    The suggestion that was made has worked. I really appreciate you're help with this.

  5. #5
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Here's a question.....

    By chance, can you put more than one data validation in there? More specifically, we force them to enter data into another cell, but can we also restrict it to just numbers and not letters?

    Thanks again..

  6. #6
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Restrict cell input based on another cell value

    Change the initial DV formula to:

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

  7. #7
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Restrict cell input based on another cell value

    This is helpful. One more problem that I have is that you have two cells. One is dependant on the other being populated. Having said that, you populate one cell, then the second and everything is fine. If you go back to the one you needed to populate and delete the number, the second number you put in still, stays put. It won't let you rewrite over it because of the DV which is good, but it still stays there.

    This negates the whole point of putting the number in the 1st cell. Is there something that can be written in there for this?+

  8. #8
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Restrict cell input based on another cell value

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

  9. #9
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Restrict cell input based on another cell value

    Is this something that you could show me how to do? I found the Macro Button and I created one called "delete" but I have no idea what the script should be??

  10. #10
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Restrict cell input based on another cell value

    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.

  11. #11
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Restrict cell input based on another cell value

    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?
    Last edited by Falk781; 07-28-2009 at 11:51 PM.

  12. #12
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Restrict cell input based on another cell value

    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.

  13. #13
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Restrict cell input based on another cell value

    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?
    Last edited by Falk781; 07-29-2009 at 01:07 AM.

  14. #14
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Restrict cell input based on another cell value

    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(TargetRange("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.

  15. #15
    Registered User
    Join Date
    07-27-2009
    Location
    Scranton, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Restrict cell input based on another cell value

    This did it, thanks; I would have never figured it out with out you. Thanks so much for your help. All I did beyond this, was placed commas to include the other cells I needed to be watching.

    You have been a real help.

Closed Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1