Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Validation list link to different sheet (Excel 2002 SP3)

    Hi,

    I want to use validation to limit the input in a specific column. I am creating a file upload to a oracle database. This has to be done in Excel. I need to prepare an upload for three oracle tables. The link of these three tables will be a customer number. So in a first sheet I am going to enter the customer numbers. This ever expanding list should be the source for validation.

    Excel does not allow a validation list with the source on a different sheet.

    Since one of the tables will be a one to many link I cannot add the different tables in one sheet.

    I hope someone has a solution for me.

    Regards Marcel de Vlieger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation list link to different sheet (Excel 2002 SP3)

    You can use a named range. Since your list of customer numbers will expand, I would use a dynamic range.
    Say that the customer numbers are on Sheet1 starting at A2 (A1 is a column header.
    Select Insert | Name | Define...
    Type a name, e.g. CustomerNumbers
    In the Refers to box, enter the formula

    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

    (if the list starts in A1, use $A$1 and omit the -1)

    Click Add, then OK.
    On the other sheet, select the cell(s) you want to apply validation to.
    Select Data | Validation...
    Select List... in the Allow dropdown
    In the Source box, enter =CustomerNumbers (use the name you assigned)
    Set the other options (Input Message, Error Alert), then click OK.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Validation list link to different sheet (Excel 2002 SP3)

    Hi Hans thanks for the solution. I will try it.

    Regards Marcel

  4. #4
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Validation list link to different sheet (Excel 2002 SP3)

    Hi Hans,

    Could you give me the dutch translation of the two functions you described?

    Regards Marcel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation list link to different sheet (Excel 2002 SP3)

    Hello Marcel,

    Here you go:

    OFFSET = VERSCHUIVING
    COUNTA = AANTALARG

    With Dutch system settings, the formula would become
    <code>
    =VERSCHUIVING(Blad1!$A$2;0;0;AANTALARG(Blad1!$A:$A )-1;1)
    </code>
    where Blad1 is the name of the sheet containing the list of allowed values.

  6. #6
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Validation list link to different sheet (Excel 2002 SP3)

    Hi Hans,

    I used this item in the sheet I am working on right now. See other posts of me this and last week.

    I have used it once and it works like a charm. But the second time it did not work.

    Please see attached file:

    In Cell B5 on Output I have validation set by means of the given solution. But it gives me a blank list of 2 rows. Is this because there are so many empty rows in the range on sheet Source data?

    Regards Marcel
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation list link to different sheet (Excel 2002 SP3)

    Yes, indeed. The range named VALIDATION is defined as

    =OFFSET('Source data'!$B$2,0,0,COUNTA('Source data'!$B:$-1,1)

    COUNTA('Source data'!$B:$ is 3 (there are 3 non-blank cells in column , so COUNTA('Source data'!$B:$-1 is 2.
    Therefore the range VALIDATION refers to B2:B3, but this range is blank.

    You should use a contiguous range of non-blank cells (i.e. no blank cells in between).

  8. #8
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Validation list link to different sheet (Excel 2002 SP3)

    Ok that is clear.
    To bad I will solve it differently.

    Thanks Marcel

Posting Permissions

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