using validation for data "on another sheet"
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: using validation for data "on another sheet"

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I've seen this tip somewhere, but can't find
    it, and need it "right now"!

    I want to control what a user enters into a
    column. I have a list of possible data
    options on another sheet in the workbook.

    Using validation "from list" demands that
    the list be on the same sheet as the cell(s)
    that are being validated.

    I know there is a simple way to bypass this
    silly limitation - can someone please remind
    me asap?! I do not want to copy the list to
    a remote area of the validation sheet, I
    just want validation to pull from the sheet
    with the data options.
    Regards, Duane
    Office2010 in Win7

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you name the range with the valid entries, you can then reference the named range instead of the range itself.

    Excel will let you access the named range, even if it is on another sheet in the same workbook.
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Tim!

    OK, I've named the range against which I want to validate...

    HOW do I enter that range name in the validation dialogue box again?

    Thanks for this clarification...
    Regards, Duane
    Office2010 in Win7

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,833
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-26 14:44, Duane wrote:
    Thanks Tim!

    OK, I've named the range against which I want to validate...

    HOW do I enter that range name in the validation dialogue box again?

    Thanks for this clarification...
    Activate the target cell, say, E2.
    Activate the option Data|Validation.
    Choose 'List' for Allow.
    Enter as Source:

    =RangeName

    Click OK.


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Excellent - thanks!
    Regards, Duane
    Office2010 in Win7

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
  •  

 

 
DMCA.com