+ Reply to Thread
Results 1 to 12 of 12

Data Validation

  1. #1
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    116

    Wink Data Validation

    This question applies to multiple sheets in a workbook: I have a daily total for the whole year(across two sheets). To keep things simple, I'll reduce it for this thread. Sheet 1 and Sheet 2 have January, 1st in cell A1 and January 2nd in A2 of both sheets. I would like, if possible to set it up so data cannot be entered for January 2nd until data has been entered for January 1st across BOTH sheets. Subsequently, January 3rd (A3) [of any sheet] will not allow data until previous dates' data are entered. . It would apply for all dates for the whole year. Is that possible, or am I being too ambitious? Thanks in advance; let me know if I need to elucidate anything and/or submit anything else! ANY help will suffice. This forum has been a tremendous help! :D

  2. #2
    Registered User
    Join Date
    08-18-2015
    Location
    Bangladesh
    MS-Off Ver
    2010
    Posts
    20

    Re: Data Validation

    Hi,

    Book1.xlsx

    in this file, the next date only appear, if data are entered in both sheets for the previous day (Data in Column C, Date appears in Column B9

    Only, if the date appears, data can be entered.

    Might not be the best way, but should work

  3. #3
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    116

    Re: Data Validation

    Hey, JesMath. Alas, it did not work. I shall attach a small workbook as a basis. Only Sheets 1 & 2 can be allowed to have data entered, and one cannot go to the next date across the workbook until a numerical valus has been entered in the previous dates.Book1Play2.xlsx
    Help me, please! I've exhausted my search! :D

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Data Validation

    Lets see if this will work for you. First I put the following formula in Sheet1!A7:A22 and Sheet2!A7:A22
    Please Login or Register  to view this content.
    then I applied the following Data Validation rules to Sheet1!B7:C22 and Sheet2!B7:C22
    Please Login or Register  to view this content.
    To add a visual clue as to which cells were available for data entry I applied Conditional formatting to Sheet1!B7:C22 and Sheet2!B7:C22
    Please Login or Register  to view this content.
    To insure that no data can be entered in Sheet3!B7:C36 I 'Locked' the cells and 'Protected' the sheet, password pets
    Here is a copy of your file with the formulas, rules and protection applied:Copy of Book1Play2.xlsx
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    116

    Post Re: Data Validation

    JeteMc: I took what you had said, and I tried to format it to my workbook, but it doesn't work. I REALLY want to learn how to do it. If I upload a similar file on which I am working, even if I have to manually change every cell, could you show me what to do? I know how to protect a sheet. I'm not worried about that. Sheet 7 is for viewing purposes only. I want the dates for the whole year to show, as indicated. Only the empty cells adjacent to the dates in Sheets 1 - 6 can have data. "Monthly" & "Yearly" totals (blue & green cells, respectively) cannot be manipulated; I'll lock those cells at the end. Remember: data cannot be entered for a date until a numerical value has been enter for the previous date. I'll do all the work if someone could steer me in the right direction. File: BOOK1_N.xlsx

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation

    P is Helper column. It can be hidden

    Range Sheet1! P2:P379 is named as CountBlankRng1
    Range Sheet2! P2:P379 is named as CountBlankRng2
    Formula for Validation (B2)
    =INDEX(CountBlankRng1,MATCH($A3-1,$A$2:$A2,0))+INDEX(CountBlankRng2,MATCH($A3-1,$A$2:$A2,0))

    Select Range(B2:L379) and apply Validation-- Custom--> Formula
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    116

    Re: Data Validation

    kvsrinivasamurthy, thank you! That is what I need. How can I apply it across the sheets? Remember: this little lady is still learning! :D

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation

    This is applied to Sheet1 and Sheet2 as per your Post1. How many sheets you want extend.
    Eg:
    If to extend for sheet3, Create helper column in Sheet3 and Name the range as CountBlankRng3

    Formula for Validation (B2)
    =INDEX(CountBlankRng1,MATCH($A3-1,$A$2:$A2,0))+INDEX(CountBlankRng2,MATCH($A3-1,$A$2:$A2,0))+INDEX(CountBlankRng3,MATCH($A3-1,$A$2:$A2,0))=0
    Similarly for more sheets.
    Last edited by kvsrinivasamurthy; 12-31-2015 at 10:31 PM.

  9. #9
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    116

    Re: Data Validation

    Thank you for trying to help me. I'm sure that, to someone who can understand and apply it, it makes perfect sense. Alas, I cannot seem to make it work to my advantage. Thanks for your help, but it doesn't work for me. My Excel hates me.

  10. #10
    Forum Contributor
    Join Date
    12-22-2015
    Location
    San Antonio, Texas
    MS-Off Ver
    2016
    Posts
    116

    Re: Data Validation

    I've had to delete some columns, too, which only exacerbate my problem. If only I could take an Excel course in one day and be fully knowledgeable.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Data Validation

    Here is a hybrid solution using kvsrinivasamurthy's idea of a helper column. In this case the helper column counts the cells in a row for the six sheets that have values. The formula for the helper column is:
    Please Login or Register  to view this content.
    count should be 66 if all of the cells on all six sheets have values, so data validation rule is:
    Please Login or Register  to view this content.
    . I have done some testing and it works as far as if there is missing data for January 1st then the user is prevented from entering data for Jan. 2nd. I have not tested to make sure that it works from one month to the next. Here is a copy of your file with the helper column and data validation rule applied:
    Copy of BOOK1_N.xlsx
    Let me know if you have any questions or if there are any issues.

  12. #12
    Registered User
    Join Date
    01-04-2016
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    42

    Re: Data Validation

    Hi I have a problem with my LOGSHEET (please see attachment). User is able to delete the contents of the cell J12 or the selection from the drop down.
    I want user to choose only from the drop down and not to delete the contents of the cell.

    What I did is locked the cell and protect. User cannot delete the contents but cant choose from the drop down contents also.

    I don't know what to do anymore. please help.


    Thanks.


    TIMETABLE - NEW.xlsm

    Capture.PNG
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 06-04-2015, 02:27 PM
  2. [SOLVED] Data Validation: How to clear/delete the content of the cell and not Data Validation List?
    By lukelucky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2015, 09:42 AM
  3. Replies: 4
    Last Post: 07-03-2014, 02:37 AM
  4. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  5. Adding Date Data Validation to cells with List Data Validation
    By biggtyme in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-03-2013, 09:47 AM
  6. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM
  7. data validation-How to set data validation for user to key in 24H time format?
    By crapit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2006, 08:00 AM

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