+ Reply to Thread
Results 1 to 20 of 20

Data Validation when the list is on a different sheet

  1. #1
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Data Validation when the list is on a different sheet

    I have found that when doing DV on one sheet and the range of data is on another sheet, it always works better to assign a range name to the data and then in the DV set up, you can then choose the range, ie. =MyRange.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    In the past, you had no choice but I agree that it is sensible and practical approach ... particularly if you define and refer to tables.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation when the list is on a different sheet

    Starting in Excel 2010, you can directly reference other sheets as the source for the drop down list.

    In addition to using a named range, you can also use the INDIRECT function:

    =INDIRECT("Sheet2!A2:A10")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    In Excel 2007 you can use, from Sheet4, say,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which has the same effect as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you define the DV List as a Table, you can use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Note that the first two options offer static Data Validation Lists, whereas the last is dynamic because it is Table based.

    See the example workbook.


    Regards, TMS
    Attached Files Attached Files

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    @Alan: thanks for the feedback.

    I perhaps shouldn't say this, but it's only fair, I picked up some examples here: How to use a table name in data validation lists and conditional formatting formulas

    And this: A Dynamic Dependent Drop Down List in Excel

    Regards, TMS

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation when the list is on a different sheet

    Hmmm...

    Yes, those work but I could swear that I've tried that (method) before and it would not accept that syntax. Had to use either a named range or the INDIRECT function.

    However, all other DV options will reject direct references to other sheets.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation when the list is on a different sheet

    So, all these years I've been misinformed!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    @Tony: you live and learn

  9. #9
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Data Validation when the list is on a different sheet

    Quote Originally Posted by Tony Valko View Post
    So, all these years I've been misinformed!
    I think Microsoft as well: http://support.microsoft.com/kb/211548
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    Not the best written piece of information I have seen

    For information, the sample workbook that I uploaded was created and tested in Excel 2007. Complete with direct and indirect "external" worksheet references (within the same workbook).

    AFAIAA, you have always been able to overcome this limitation by specifying a Named Range.

  11. #11
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Data Validation when the list is on a different sheet

    Quote Originally Posted by TMShucks View Post
    AFAIAA, you have always been able to overcome this limitation by specifying a Named Range.
    Yes, with all versions I think. I do not think I have seen before that you may use direct references to other sheets in 2007. Indeed I have seen many pages that say you may not. Perhaps it has been changed with a service pack or hotfix?

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    King James Version text (John 20:24-29) is:

    24 But Thomas, one of the twelve, called Didymus, was not with them when Jesus came.
    25 The other disciples therefore said unto him, We have seen the LORD. But he said unto them, Except I shall see in his hands the print of the nails, and put my finger into the print of the nails, and thrust my hand into his side, I will not believe.
    26 And after eight days again his disciples were within, and Thomas with them: then came Jesus, the doors being shut, and stood in the midst, and said, Peace be unto you.
    27 Then saith He to Thomas, Reach hither thy finger, and behold my hands; and reach hither thy hand, and thrust it into my side: and be not faithless, but believing.
    28 And Thomas answered and said unto him, My LORD and my God.
    29 Jesus saith unto him, Thomas, because thou hast seen Me, thou hast believed: blessed are they that have not seen, and yet have believed.

    As I said, the workbook uploaded was created and tested in Excel 2007. No doubt you can download it and prove for yourself that it can be done ... in Excel 2007.


    In reality, a Dynamic Named Range was, and is, a better way of setting up a Data Validation List. Just demonstrating the fact that it CAN be done using references to "external" sheets, both directly and indirectly.

    Regards, TMS

  13. #13
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Data Validation when the list is on a different sheet

    I do not doubt what you have said, I only wonder if this has been changed with a service pack or update from the original 2007 behaviour, where I have seen it fail.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation when the list is on a different sheet

    Quote Originally Posted by Izandol View Post
    I think Microsoft as well: http://support.microsoft.com/kb/211548
    Yeah, I saw that page.

    The scenario described on that page is not exactly the same. On that page they describe using the Data Validation>Custom option which will not allow direct references to other sheets in Excel 2007. That is still true. That also applies to all of the other selectable options EXCEPT for the List option.

    Apparently you CAN use references to other sheets as the source for the Data Validation>List option. Although, I have to say, ever since Excel 2007 came out I was under the impression that you could not even do that. So, as I noted in my other post, I've been misinformed about that since Excel 2007 was released.

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation when the list is on a different sheet

    Quote Originally Posted by Izandol View Post
    I only wonder if this has been changed with a service pack or update from the original 2007 behaviour, where I have seen it fail.
    I'm kind of thinking the same thing.

    As much as I use Excel I can't imagine that I have NEVER tried doing this in Excel 2007.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    My apologies. It was not directed at you personally ... but I just couldn't resist. My wife often tells me that I should try to resist, often and specifically when I say things out loud that I really should have just kept in my head.

    But I would be grateful if people do test this ... I'm beginning to doubt it myself.

    Regards, TMS

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    I guess I should make the point that you actually have to type the external sheet reference, for example,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    .


    Whereas, you can navigate to the range on the same sheet ... perhaps that's been the show stopper?


    Regards, TMS

  18. #18
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation when the list is on a different sheet

    Quote Originally Posted by TMShucks View Post


    Whereas, you can navigate to the range on the same sheet ... perhaps that's been the show stopper?

    Yes, if it's on the same sheet then you can point to the source.

    With the DV userform open to the List option, in the Soruce refedit, when I type the equal sign then try to point (navigate) to the source on another sheet nothing happens. Then I have to manually type the source address (or named range) and that works.

  19. #19
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Data Validation when the list is on a different sheet

    Quote Originally Posted by TMShucks View Post
    I guess I should make the point that you actually have to type the external sheet reference, for example,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    .


    Whereas, you can navigate to the range on the same sheet ... perhaps that's been the show stopper?
    Very interesting - this is the key. Even with a new Office 2007 with no updates or service packs this is same behaviour. A nice discovery!

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Data Validation when the list is on a different sheet

    So it would seem that the limitation is purely in the fact that you can't use cell selection, that is, navigate to the cells you want to use as the list. You have to manually type the reference in.

+ 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. List Sheet Names in Data Validation List without VBA
    By Al Chara in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-30-2013, 01:49 AM
  2. Replies: 11
    Last Post: 10-06-2011, 02:26 PM
  3. Replies: 1
    Last Post: 10-05-2011, 04:57 PM
  4. Replies: 7
    Last Post: 11-11-2010, 09:51 PM
  5. Data Validation list from another sheet
    By p.iline in forum Excel General
    Replies: 3
    Last Post: 08-05-2008, 09:57 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