Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Data validation across worksheets/workbooks

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can I validate data across workbooks?
    I'd like to set up an data entry form, where the user has to assign a unique ID to an item. To make sure the ID is a unique one, I thought I could transfer previously assigned IDs into a column *of another workbook*, and set a validation rule in the worksheet form's data entry cell that validates that the chosen number is not present among those in the column in the column of the other workbook. To my disappointment, Excel told me that data validation cannot be used across worksheets/workbooks. Is there a workaround, or can anyone think of another way to warn the user that a previously assigned ID has been entered ???

    Thanks for your help!!
    Klaus

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-26 04:15, Kloot8 wrote:
    How can I validate data across workbooks?
    I'd like to set up an data entry form, where the user has to assign a unique ID to an item. To make sure the ID is a unique one, I thought I could transfer previously assigned IDs into a column *of another workbook*, and set a validation rule in the worksheet form's data entry cell that validates that the chosen number is not present among those in the column in the column of the other workbook. To my disappointment, Excel told me that data validation cannot be used across worksheets/workbooks. Is there a workaround, or can anyone think of another way to warn the user that a previously assigned ID has been entered ???

    Thanks for your help!!
    Klaus
    Use a named range for your data validation range. ie. name your range in the other sheet
    and use this in your list.


    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan,

    What is 'named range' ??

    (Sorry for the dumb question)

  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

    Kloot,

    (from the film ?)

    a named range is a range of cells which you assign a name...

    so rather than a range of A1:Z100, you can call it "Klootsummary" or something more appropriate

    to facilitate this :

    insert
    names
    define

    and follow the boxes


    :: Pharma Z - Family drugstore ::

  5. #5
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hello there

    try this

    http://www.mrexcel.com/board/viewtop...orum=2&start=0

    i think it helps a lot


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
  •