+ Reply to Thread
Results 1 to 8 of 8

Offset Indirect Data Validation

  1. 05-14-2015, 06:39 AM #1
    jwillis07 is offline
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel for O365
    Posts
    197

    Offset Indirect Data Validation

    Hi all,

    Could anyone tell me why this doesn't work?

    There's two sheets, one called 'Named Ranges' and one called 'Data Validation'

    In all but one of the named ranges I've used the offset formula so that I could add to list and it appear in the drop down list. Whilst I can add to the list and it will expand each named range, the drop down box usuing indirect data validation doesn't display the range.

    In the one range where I haven't used the offset formula (Premier League) - that one does work in the drop down list but it has blanks at the end (what I'm trying to avoid).

    Any help greatly appreciated.

    Jason
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-14-2015, 07:16 AM #2
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Offset Indirect Data Validation

    Hi there. I have fixed Bundesliga and Premier League for you. Try the rest yourself. If you hit a wall - come back.

    I've removed the leage name from the first row (B & C).
    Cell B1 is now named Premier_League and the entire column named Premier_League_Col

    Similarly in C.

    The data validation formula has been changed to =OFFSET(INDIRECT(SUBSTITUTE($C2," ","_")),0,0,COUNTA(INDIRECT(SUBSTITUTE($C2," ","_")&"_Col")),1)

    Which gets rid of any spaces, substituting them with an underscore and adding _Col at the end.
    Attached Files Attached Files
    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
    Reply With QuoteRegister To Reply
  3. 05-14-2015, 07:47 AM #3
    jwillis07 is offline
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel for O365
    Posts
    197

    Re: Offset Indirect Data Validation

    Glenn,

    That's brilliant, thank you! Managed to complete the other leagues then applied the concept to what I'm working on first time.

    Again, thank you very much!

    Jason
    Reply With QuoteRegister To Reply
  4. 05-14-2015, 08:09 AM #4
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Offset Indirect Data Validation

    Woo Hooo! Another satisfied "customer". Thanks for the Reputation. It's appreciated.
    Reply With QuoteRegister To Reply
  5. 02-22-2019, 04:52 AM #5
    markdaniels is offline
    Registered User
    Join Date
    02-22-2019
    Location
    Warrington, England
    MS-Off Ver
    2010
    Posts
    2

    Re: Offset Indirect Data Validation

    Great download, is there a way to combine the calculation so it searches also so that when you type "Villa" for example it narrows down?

    =OFFSET(INDIRECT(SUBSTITUTE($C2," ","_")),0,0,COUNTA(INDIRECT(SUBSTITUTE($C2," ","_")&"_Col")),1)
    Reply With QuoteRegister To Reply
  6. 02-22-2019, 05:18 AM #6
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,774

    Re: Offset Indirect Data Validation

    Welcome to the forum!

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 02-22-2019, 05:27 AM #7
    markdaniels is offline
    Registered User
    Join Date
    02-22-2019
    Location
    Warrington, England
    MS-Off Ver
    2010
    Posts
    2

    Re: Offset Indirect Data Validation

    Seemed a straightforward question linked to what was requested but ok
    Reply With QuoteRegister To Reply
  8. 02-22-2019, 05:35 AM #8
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,774

    Re: Offset Indirect Data Validation

    It's a straightforward forum rule - link at the top of the page. Thanks for your co-operation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Data validation using indirect
    By erisaaka in forum Excel General
    Replies: 1
    Last Post: 02-11-2014, 08:16 PM
  2. Data Validation with Indirect function referring to Offset named range = Error.
    By cool1_boy1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2013, 08:26 AM
  3. Indirect Data Validation
    By techteam in forum Excel General
    Replies: 3
    Last Post: 11-22-2011, 07:03 AM
  4. Data Validation and Indirect
    By matt_the_brum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2006, 09:53 AM
  5. [SOLVED] Data Validation with Indirect
    By Nigel in forum Excel General
    Replies: 3
    Last Post: 12-17-2005, 11:40 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1