Join us now. It is free, and it takes less than 1 minute to register.
Register now
Subscribe to our daily newsletter. It is free, and it comes with many benefits.


+ Reply to Thread
Results 1 to 5 of 5

Thread: Excel: Data Validation

  1. #1
    Senior Member
    Join Date
    Aug 2011
    Location
    Somewhere close to you
    Posts
    315
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel: Data Validation

    Quick question to you excel fundis as Google let me down.

    I have column of calculated dates which range from current to plus minus 5 years to the future. When using data validation all the dates come up. From which only 10 dates would be relevant at any given time. As time goes on the later dates would be relevant but scrolling down would be a pain in the ass.

    Is there any way to limit the dates to later than a specific date and maybe the next10 max 20 entries after the date.

    In other words a formula which look at a date (date x) on one sheet (Sheet a), then select from a specific column in Sheet b the 1st date after this date (date x) as well as the following 9-11 entries. These dates would then populated in column in sheet c.

    I would then use data validation (list) to this column of dates.

  2. #2
    Super Grandmaster KT-B's Avatar
    Join Date
    Feb 2014
    Location
    Pretoria
    Posts
    8,531
    Mentioned
    33 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can do this with VBA but not sure you can do it strictly with data validation. I am assuming you want the list to show date +10 consecutive dates? You could basically only need code to capture the selected/current date. Forumulas could be used to create all the other dates for your validation list.
    And crawling on the planet's face
    Some insects called the human race
    Lost in time, and lost in space
    And meaning

  3. #3
    Grandmaster
    Join Date
    Dec 2005
    Location
    Randburg
    Posts
    1,087
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Quote Originally Posted by rh1 View Post
    Quick question to you excel fundis as Google let me down.

    I have column of calculated dates which range from current to plus minus 5 years to the future. When using data validation all the dates come up. From which only 10 dates would be relevant at any given time. As time goes on the later dates would be relevant but scrolling down would be a pain in the ass.

    Is there any way to limit the dates to later than a specific date and maybe the next10 max 20 entries after the date.

    In other words a formula which look at a date (date x) on one sheet (Sheet a), then select from a specific column in Sheet b the 1st date after this date (date x) as well as the following 9-11 entries. These dates would then populated in column in sheet c.

    I would then use data validation (list) to this column of dates.
    Presumably you are using a "List" data validation control. So, for example to validate input in cell A1 when your date list is in column B (from row 1) just set the "Source" field to something similar to this:

    =OFFSET(B1,MATCH(TODAY()+1,B:B,0),0,10) for the next ten dates
    =OFFSET(B1,MATCH(TODAY()+1,B:B,0),0,20) for the next twenty dates

    You should be able to adapt this for your situation.
    Only two things are infinite: the universe and human stupidity, and I'm not sure about the former.

    --Albert Einstein

  4. #4
    Super Grandmaster KT-B's Avatar
    Join Date
    Feb 2014
    Location
    Pretoria
    Posts
    8,531
    Mentioned
    33 Post(s)
    Tagged
    0 Thread(s)

    Default

    OP I would love to see the solution when you have it. I had not even considered the above.
    And crawling on the planet's face
    Some insects called the human race
    Lost in time, and lost in space
    And meaning

  5. #5
    Senior Member
    Join Date
    Aug 2011
    Location
    Somewhere close to you
    Posts
    315
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. Will attempt tomorrow and provide feedback.

+ Reply to Thread

Similar Threads

  1. Import data into Excel
    By ScottulusMaximus in forum Questions and Answers, Problems and Solutions
    Replies: 1
    Last Post: 08-08-2016, 08:30 PM
  2. Excel 2008 (Mac) - 1.7MB file, had data, but now no data?
    By PostmanPot in forum PC Hardware and Gadgets
    Replies: 1
    Last Post: 18-02-2014, 12:00 AM
  3. I need a different method to import data into excel from txt
    By foozball3000 in forum Software and Web Development
    Replies: 14
    Last Post: 16-09-2009, 11:56 AM
  4. Excel Data Connections
    By foozball3000 in forum Software and Web Development
    Replies: 7
    Last Post: 04-06-2009, 02:03 PM
  5. Excel 2007 Data Plotting
    By Maelly in forum Off Topic
    Replies: 6
    Last Post: 23-07-2008, 08:36 PM

Bookmarks

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
  •