+ Reply to Thread
Results 1 to 10 of 10

Data Validation

  1. #1
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Data Validation

    Hi, in a cell i have data validation from a range. The problem is that, in the drop down list, the data of area appears at row by row (A2, B2, C2 ...). What should I do to appear contents during column by column (A2, A3, A4, B2, B3, B4 ...)? Thanks in advance.
    Attached Files Attached Files
    Last edited by Immortal2014; 12-17-2015 at 06:09 AM.

  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,893

    Re: Data Validation

    Sorry, I am not sure that I understand your explanation. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon
    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
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Data Validation

    Ok thanks for info, the attachment is ready.

  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,893

    Re: Data Validation

    Hi. I'm not sure if I know the answer to that... One way (maybe a bit of a cheat) is to use this formula in a hidden column or sheet, to assemble the DV list in the order that you want and then use that in your named range:
    =OFFSET($B$3,MOD((ROWS($1:1)-1),3),INT((ROWS($1:1)-1)/3),,)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Data Validation

    Thanks for your answer Glenn, i will test your solution and i will come back in a while.

  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,893

    Re: Data Validation

    Ok, but it will NOT work if the list in your real data more than 3 rows long. If it is (for example) 6 rows long, change these 3s to 6s

    =OFFSET($B$3,MOD((ROWS($1:1)-1),3),INT((ROWS($1:1)-1)/3),,)

  7. #7
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Data Validation

    Yes!! It works perfect, thank you very much Glenn!

  8. #8
    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,893

    Re: Data Validation

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

  9. #9
    Forum Contributor
    Join Date
    09-18-2014
    Location
    Χαμένος στο διάστημα
    MS-Off Ver
    2016
    Posts
    383

    Re: Data Validation

    My pleasure! A question for scrolling.. Is it possible to scroll in the drop down list?

  10. #10
    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,893

    Re: Data Validation

    There should be a scroll bar at the right of the dropdown....

+ 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

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