Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Validation list on separate worksheet

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! Is it possible to validate cells in one worksheet using a drop-down list where the source data is contained in another worksheet.

    At the moment, I have a workbook where I maintain the same validation source list in multiple worksheets rather than one master list (actually I do use a master list but I have lookup lists in the other worksheets.

    Thanks!

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure, Shane. Here's what I do:

    Select the first value in the validation list. Hit Shift-Ctrl-Down arrow until you're all the way down to row 65,536. Hit Insert-Name-Define and call it something like MyValidList.

    When you set the data validation, type exactly this:

    =MyValidList

    into the range box. Works great!
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're absolutely right - thanks a lot!

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you may run into problems in the future if you delete anything from this list (or, say, accidentally enter something on row 65,536 and then delete it) in so far as it will make the dropdown box massive due to the fact that that last cell has been "validated" (no pun intended). You'll get your list followed by 65,000 rows of blanks !

    it might be wise to slightly augment Dreamboat's excellent suggestion by using a dynamic range name rather than the entire column....

    if your list sits in column C (for example) name it as she suggests but enter as "refers to" the following :

    =OFFSET($C$1,0,0,MATCH(REPT("z",255),$C$C))

    the named range will expand and contract accordingly and keep your data validation box blank-free


    :: Pharma Z - Family drugstore ::

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if your list sits in column C (for example) name it as she suggests but enter as "refers to" the following :

    =OFFSET($C$1,0,0,MATCH(REPT("z",255),$C$C))

    the named range will expand and contract accordingly and keep your data validation box blank-free
    Hi Chris:
    Your suggested solution augmenting Dreamboat's works beautifully -- Just one editorial correction -- need for added colon at $C$C

    =OFFSET($C$1,0,0,MATCH(REPT("z",255),$C:$C))

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris, that's a great suggestion for the perfect solution! A couple of points though.

    My list is in column A so in the "Refers To" field I have entered:

    =OFFSET($A$1,0,0,MATCH(REPT("z",255),$A$A))

    This is causing an error message stating a formula error. Any idea why this may be.

    Your formula is impressive (to me at least!). I'd be delighted if you could break it down for me a little and explain so I can understand the logic for future reference - thanks Chris!

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-10 14:01, Shane wrote:
    Chris, that's a great suggestion for the perfect solution! A couple of points though.

    My list is in column A so in the "Refers To" field I have entered:

    =OFFSET($A$1,0,0,MATCH(REPT("z",255),$A$A))

    This is causing an error message stating a formula error. Any idea why this may be.

    Your formula is impressive (to me at least!). I'd be delighted if you could break it down for me a little and explain so I can understand the logic for future reference - thanks Chris!
    Hi Shane:
    First to get you going, change $A$A in Chris' formula to $A:$A -- see my post above.
    Then as far as breaking the formula down is concerned, let me become Chris' mouthpiece, although Chris would be best at it --

    What the formula is doing is 1)starting the beginning of the list range at cell C1, 2) then it finds the end of the list by matching it to a record that is equal to or smaller than a string of 255 Zs, and it does it dynamically.
    I am sure Chris would be delighted to add some more explanation to it!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good spot Yogi buddy!

    (You're a hard taskmaster...hee hee)

    I couldn't have explained it better myself, so thanks for that, I had long since gone to bed over here...

    I should add though, the matching 255 "z" characters was suggested by Mark W and Aladin after I ran into problems with my dynamic ranges, originally from the very useful set that are up on Dave's pages at http://www.ozgrid.com

    A beauty from which suggests that by adding a fourth offset arguement ALSO of the match(rept("z",255 etc etc makes your range dynamic horizontally - so you end up with a dynamic range that ebbs and flows not only downwards but along too, just far too handy !





  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Dublin, Ireland
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Guys, works perfectly - I'm well impressed!

    One question, what exactly does the function "REPT" do?

    Thanks so much!

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-11 02:02, Shane wrote:
    Guys, works perfectly - I'm well impressed!

    One question, what exactly does the function "REPT" do?

    Thanks so much!
    =REPT("z",255)

    creates a string of 255 z's.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •