+ Reply to Thread
Results 1 to 7 of 7

Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

  1. #1
    Registered User
    Join Date
    03-16-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    11

    Question Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

    This is (almost) EXACTLY what I need....but I just a little help on one part of the VBA code (who I have to thank VoG over on the Mr.Excel forums for figuring out)

    I am making an Expense spreadsheet for work and I want it so that they cannot enter in an amount for their expense unless they enter their date, their name, the reason for the expense, the client they were working on that generated the expense, and the type of expense. So columns H & J are the cells I want them unable to enter in data for unless they select from they enter information in the cells on the same row in columns B, C, D, E, and H.

    I was able to modify the code so it would protect the cells in columns I & K and it works perfectly but ONLy if column D is filled in .....but I need to also have it check columns B, C, E, and H as well.

    So how do I change
    Please Login or Register  to view this content.
    to include making columns B,C, E, and H also have text entered?

    I need to have this finalized for a presentation on Monday morning....so any late night or weekend help would be GREATLY appreciated!

    Please Login or Register  to view this content.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

    You don't need VBA for this at all. You can accomplish the same thing with Data Validation. For example:
    1) Select J2.
    2) Open the Data Validation windowand use the following settings:

    Settings Tab:
    --Allow: Custom
    --Formula: =COUNTA($B2:$E2, $H2)=5

    Error Alert Tab:
    --Style: Stop
    --Title: Fill in cells
    --Error Message: Fill in B, C, D, E and H before trying to fill in this cell



    That's it. Now J2 can't be edited unless those cells are all filled in. Apply this same technique as needed across this first row. Then copy those cells downward to apply the rest of the range needed.
    _________________
    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
    Registered User
    Join Date
    03-16-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

    Thank you for the FAST reply....I am certainly going to save your post for future reference, BUT.......

    I am using Data Validation to make sure that they actually enter in a number and not text. Can I combine that with your suggestion above?

    I currently have it set as

    Settings Tab:
    ---Decimal
    ---Greater or Equal To
    ---Pointing a cell with 0 in it

    Then I have my error messages saying to please enter a $ amount.

  4. #4
    Registered User
    Join Date
    03-16-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

    BTW.... I just tried your formula and it worked GREAT!!!! Now just to add a part to the formula to make sure they enter a number (not text) = to or greater than zero...

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

    You can probably use an AND() clause to add to your custom formula that makes sure the value added IN J2 is a number.

    =AND(COUNTA($B2:$E2, $H2)=5, ISNUMBER($J2))


    --Error Message: Fill in B, C, D, E and H, then enter a NUMBER only in this cell



    You can add more inside the AND(), more tests, like $J2>0....

  6. #6
    Registered User
    Join Date
    03-16-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

    Thank you again, it worked perfectly! Simple and effective. The easiest thing about Excel is overcomplicating what you want to do!!!!

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Preventing Data Entry Unless Other Columns on That Same Row Are Filled Out First

    I have marked this thread solved for you.
    In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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