+ Reply to Thread
Results 1 to 9 of 9

Data Validation for Row in a Table

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

    Data Validation for Row in a Table

    I included a workbook example to this post. The data validation needs to be expandable as the table will expand over time. The validation would be in column I "Batch Release Submission Date." When a user places a value [date] in the cell, it will only allow the entry IF the row of the selected cell has entries for all columns A through H and J through N.
    Example: I have I6 selected, and I want to put a date in it. According to the table, F6 and M6 do not have anything in their respective cells. An error message would alert the user that the respective row needs to be filled prior to an entry in column I.
    I iterate: The table will grow over the course of a year, so the validation needs to grow with it. Is that possible?
    Thanks, experts!
    Attached Files Attached Files
    Last edited by STBTC; 12-03-2016 at 11:45 AM. Reason: error

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Data Validation for Row in a Table

    If you add the next row by adding something in a column without DV (e.g. column B), the DV extends into the new row in (e.g.) column A. What's the problem?

    I can't test it on your sheet, as you deleted the sheet DATA before posting. Should work OK, though...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    Re: Data Validation for Row in a Table

    What would be the data validation formula? Do I select cells I2 to I9, click Data Validation, select custom, and put a formula in it? If so, what, and would it automatically update when a new row is added at the bottom of the table that joins it? There are the "Ignore Blank" and "Apply these changes to all other cells with the same settings" boxes, too. I’m really lost.
    Last edited by STBTC; 12-03-2016 at 02:07 PM. Reason: Omission

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Data Validation for Row in a Table

    I am REALLY hampered here. You have already got DV formulae in columns A, F, G & J in the sheet that you posted. Except that they don't work in the version you posted, cos the Named ranges that you used to set up the DV no longer exist on the sheet you posted. They were on the sheet called Data. You must have deleted that sheet before posting.

    on your REAL sheet, if you enter something on the FIRST EMPTY ROW of any column in the Table that is NOT A,F, G & J the DV already in those columns should copy down into the newly extended Table.

    In the sheet attached here, you'll see that there is some DV in column A, restricting choice to the list in orange (I2 to I7). at the moment, the DV does not extend into A9.... Now type anything into B9. Two things happen. The Table extends to include row 9 AND the DV now also operates in A9.

    Do something analogous in your REAL sheet. What happens?
    Attached Files Attached Files

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

    Re: Data Validation for Row in a Table

    Quote Originally Posted by Glenn Kennedy View Post
    I am REALLY hampered here. You have already got DV formulae in columns A, F, G & J in the sheet that you posted. Except that they don't work in the version you posted, cos the Named ranges that you used to set up the DV no longer exist on the sheet you posted. They were on the sheet called Data. You must have deleted that sheet before posting.

    on your REAL sheet, if you enter something on the FIRST EMPTY ROW of any column in the Table that is NOT A,F, G & J the DV already in those columns should copy down into the newly extended Table.

    In the sheet attached here, you'll see that there is some DV in column A, restricting choice to the list in orange (I2 to I7). at the moment, the DV does not extend into A9.... Now type anything into B9. Two things happen. The Table extends to include row 9 AND the DV now also operates in A9.

    Do something analogous in your REAL sheet. What happens?
    I attached the workbook with the data sheet, Glenn. You are saving my life if you can help me! Ha ha Let me know if you need anything else! If you can fix it the way I need, PLEASE elucidate the steps as I would LOVE to learn!
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Data Validation for Row in a Table

    OK. Now it all works. the DV in column f was broken, so I've fixed it. if other responses are needed, add them in Data.

    For column i, you can only enter something in I if the 13 cells around it are non-blank (A-H and J-N). if you ad a new row (edg by typing something in B12, all the DV applies to the new table row.
    #
    i used this as the DV formula in column I:
    =COUNTA(A2:H2,J2:N2)=13
    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 for Row in a Table

    Okay, so, for my workbook, I’ll delete the data validation for column F, highlight column F save row one, click data validation, format it as list, apply =NameDynamic (the dynamic range I would want) in the formula bar, and press enter. I will then highlight column I in the table save row one, click data validation, select custom and put the formula =COUNTA(A2:H2,J2:N2)=13 in the formula bar, and press enter. Is that correct?
    I tried it, and it doesn’t work. May I send the actual file to you directly? I need your help.
    Also, it doesn’t matter if column J is populated. It may or may not be populated depending on factors with the client.
    The validation wouldn’t directly reference row 2 would it? Some days I7 would have a date, then three days later, I two would finally have a date.
    Last edited by STBTC; 12-03-2016 at 04:38 PM. Reason: Additions, Grammar

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

    Re: Data Validation for Row in a Table

    I fixed it: I had to uncheck "Ignore blank," change J2:N2 to K2:N2 (as column J may or may not have a value), and change 13 to 12 to reflect it.
    Thanks!
    I do have one last question about it, but since it is a different question, I’ll create a new thread.
    Thanks, Glenn!

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Data Validation for Row in a Table

    You're welcome... you're learning... and thanks for the Rep.

+ 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: 1
    Last Post: 06-24-2016, 04:13 PM
  2. Replies: 0
    Last Post: 10-10-2015, 09:51 AM
  3. [SOLVED] How do I link Dropdown List (data validation) with filtering data from a table?
    By Trishux25 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-29-2015, 10:17 PM
  4. Replies: 1
    Last Post: 07-29-2015, 08:36 PM
  5. [SOLVED] Data Validation List to Return Variable/Dynamic Data based on Reference Table
    By dilbert1865 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2014, 09:08 AM
  6. Data Validation from Table (Non-VB)
    By Sleeper in forum Excel General
    Replies: 4
    Last Post: 08-25-2009, 05:23 PM
  7. Replies: 10
    Last Post: 01-30-2006, 09:35 PM

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