+ Reply to Thread
Results 1 to 6 of 6

Data Validation: using date in as initial date in range

  1. #1
    Registered User
    Join Date
    02-11-2014
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Data Validation: using date in as initial date in range

    I am looking to use data validation to make sure that dates entered into the spreadsheet fall into a specific range based on an initial date.
    Specifically, I want the date entered into column L to be within 3 days of the date entered into column A*. PTSDCP Participant Tracking.xlsx
    I have tried using an absolute reference on column A ($A2) so that L2 refers to the date in A2, and L3 refers to the date in A3 ect, but so far the data validation only works for L2 and not any rows below it.
    How can I resolve this issue and only use one data validation command (so that it continues referencing each date in the column without creating an individual command for each date)?

    Thanks,

    dmorl


    **I also want the date entered into column O to be within 7 days of the date entered into column A, and the date entered into column Q to be within 14 days of the date entered in column A**

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Data Validation: using date in as initial date in range

    what aspect of the data validation is not working for rows below it?

    when i copy l2 and paste it into l3 the relative data validation reference adjusts... (i.e. it references a3 instead of a2)
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito

  3. #3
    Registered User
    Join Date
    02-11-2014
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Data Validation: using date in as initial date in range

    7/5/15 is the current date in A3. If the validation was working correctly and I typed 07/20/15 into L3, I should be stopped from entering that date because it falls beyond the 3 day range. However that is not the case-- can just enter that date without a problem. Try it yourself: put in a date into A4 (that is past 07/01/15), and then put in a date into L4 that is more than 3 days past the date in A4. The validation should prevent you from entering it and prompt you to enter another date, but it doesn't.

  4. #4
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Data Validation: using date in as initial date in range

    your validation seems to have gotten off a row... make sure the validation in L2 corresponds to A2... after correcting for that it works as you described...

  5. #5
    Registered User
    Join Date
    02-11-2014
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Data Validation: using date in as initial date in range

    ...oh duh, I had it from $A2. When I changed it to $A1 as the start date and $A1+3 as the end date it fixed the problem.
    Thanks for bearing with me!

  6. #6
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Data Validation: using date in as initial date in range

    glad i could help, 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. [SOLVED] Macro to match date in data table and copy cell range containing date.
    By 54ed in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2014, 04:52 AM
  2. [SOLVED] Data Validation won't accept Start Date and End Date from cell values.
    By SpecialBrew in forum Excel General
    Replies: 12
    Last Post: 03-16-2014, 10:23 PM
  3. [SOLVED] Creating an average from a set of data based on an initial start and end date and time
    By MKF2013 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2013, 02:01 PM
  4. Replies: 2
    Last Post: 12-31-2011, 02:44 AM
  5. [SOLVED] Need date to appear 90 days later than initial date entered
    By Becky in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2005, 10:05 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