+ Reply to Thread
Results 1 to 15 of 15

Googl Sheet : Data validation If Not Due Date

  1. #1
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Googl Sheet : Data validation If Not Due Date

    hi all.

    how to make formula in data validation for this condition:
    in col B contains type of rent car like toyota, honda, etc. with input start date & end date (due date)
    i want to if when i choice in cell B2 e.g. toyota and not yet due date (col D2) so when i choice again toyota in cell B3 the system is not allowed cause still not due date (1/4/2022)
    my desired result in cell b2 down

    please, check this link sharing
    https://docs.google.com/spreadsheets...it?usp=sharing

    for anyone help, thank in advance

    john m
    Last edited by Jhon Mustofa; 01-17-2022 at 10:51 PM.

  2. #2
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Re: Googl Sheet : Data validation If Not Due Date

    cross posting from https://www.mrexcel.com/board/thread...-date.1193225/

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

    Re: Googl Sheet : Data validation If Not Due Date

    Perhaps apply the following custom formula as a data validation rule to cells B3:B4 =and(C3<>"",C3>max(D$2:D2))
    However, it appears that you cannot use a custom formula and list (drop down) in the same cell.
    Note that when I typed the formula into data validation it appears it was saved to drive.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Re: Googl Sheet : Data validation If Not Due Date

    hi JeteMc, thank but not fully work, when i input in cell B3 with "Mercy" should be enter.
    if the same name cannot enter with due date criteria

  5. #5
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Googl Sheet : Data validation If Not Due Date

    unless you can explain how the vehicle becomes blocked AND unblocked, you will not get a good solution.

  6. #6
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Re: Googl Sheet : Data validation If Not Due Date

    the vehicle can't same if date in cell D2 is not Due Date.
    it mean, vehilce will be blocked if the same name vehicle and NOT due date
    if different name vehicle, so Unblocked

  7. #7
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Googl Sheet : Data validation If Not Due Date

    Ok, to block the input of vehicle until after date is input, and only allow same vehicle if date is not crossover, this can not be done properly with Data Validation. you will need some apps scripts to accomplish this.

  8. #8
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Re: Googl Sheet : Data validation If Not Due Date

    hi janmorris,....thanks for your suggestion.
    i hope someone would give me a scripts to figure out this problem.

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

    Re: Googl Sheet : Data validation If Not Due Date

    I can show you a way this might be done in Excel attempting to use functions that I believe are available in google, so that you can make the transition to google.
    The list of all cars that can be rented is in column M
    Column N displays the last "End Date" for that car using*: =IFERROR(LARGE(IF(B$2:B$21=M2,D$2:D$21),1),"")
    Column O displays the availability of that car based on the next "Start Date" in column C using: =OR(N2="",N2<R$1)
    Column P makes a list for the data validation drop down using*: =IFERROR(INDEX(M$2:M$5,SMALL(IF(O$2:O$5,(ROW(M$2:M$5)-ROW(M$1))),ROWS(P$2:P2))),"")
    Cell R1 shows the next "Start Date" using: =MAX(C2:C20)
    The source for the data validation drop downs in column B is: =OFFSET(P$2,0,0,SUMPRODUCT(--(P$2:P$5<>"")))
    Note that when the drop down in cell B3 is selected, Toyota is not listed as it is not available for 1/3/2022
    * The formulas in columns N and P are array formulas.
    I hope that this helps.

  10. #10
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Re: Googl Sheet : Data validation If Not Due Date

    hi JeteMc, thank you so much..
    thank for your kindness.

  11. #11
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Re: Googl Sheet : Data validation If Not Due Date

    hi JeteMc..
    how is if reverse order, i mean at first request start date then type car
    please, check new update file, in yellow range
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Googl Sheet : Data validation If Not Due Date

    although this has been marked as SOLVED.....

    G-Sheets wont accept the OFFSET formula to create the range:
    =OFFSET(P$2,0,0,SUMPRODUCT(--(P$2:P$5<>"")))
    fortunately, the known workaround for this in G-Sheets is to use a named range... so that isn't an issue.


    however, for both Excel 2019 (macos) and G-Sheets, the solution offered falls over in a couple of ways:
    • vehicles are unavailable when date range is before any others, so the system must be used chronologically
    • after the first vehicle, the other vehicles can be chosen multiple times for the same date range

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

    Re: Googl Sheet : Data validation If Not Due Date

    As to post #11 the attached file shows how the proposal from post #9 can be modified (see columns T:Y) to fill the drop downs in column H.
    Let us know if you have any questions.
    By the way, according to the linked article the OFFSET function is supported by google sheets: https://blog.sheetgo.com/google-shee...google-sheets/
    However perhaps it cannot be used as the source for the data validation, I am not that familiar with google sheets, just trying to off some help.
    Certainly, if anyone has a solution, I am sure that Jhon would appreciate seeing it. The more the merrier.

  14. #14
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 & Google Sheet
    Posts
    1,146

    Re: Googl Sheet : Data validation If Not Due Date

    hi JeteMc, thank you so much.

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

    Re: Googl Sheet : Data validation If Not Due Date

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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: 05-20-2017, 10:29 AM
  2. Replies: 5
    Last Post: 04-06-2016, 06:58 AM
  3. Replies: 2
    Last Post: 08-24-2014, 05:25 PM
  4. [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
  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. Replies: 4
    Last Post: 05-17-2012, 04:40 PM
  7. data validation-Can the source of the validation criteria be dfrom a different sheet?
    By yael pinkert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2009, 04:28 AM

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