Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    validation in sheets (excel 2003)

    Hello Everyone,

    I am in the process of creating a validation, but I am unable to select where the data is going to be. Is it possible to have the validation be from a different sheet. For example, the validation is a pull down list with the values coming from sheet1 and the validation on sheet 2. Any help would be great.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation in sheets (excel 2003)

    Sure.

    - Put your list on any sheet
    - select the cells
    - Insert, name, define: give it a meaningful name (e.g. ValidationListNames)
    - go to the cells that need the validation and select Data, validation. list
    - enter =ValidationListNames in the lower box.
    - Again goto data validation and make sure Excel didn't change your formula to ="ValidationListNames", if so, remove the quotes.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: validation in sheets (excel 2003)

    You cannot refer directly to a list on another worksheet in validation, but you can use a named range that refers to this list:
    - Select the list of allowed values.
    - Select Insert | Name | Define and type a name, then click OK.
    - Alternatively, type the name in the cell address box on the left hand side of the formula bar.
    - Switch to the worksheet where you want to add validation.
    - Select the cell(s), then select Data | Validation...
    - Select List from the Allow dropdown list.
    - Type = followed by the name you just defined, for example =MyNames
    - Set the other options.
    - Click OK.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation in sheets (excel 2003)

    Hah. I beat you to it Hans <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation in sheets (excel 2003)

    Thank you both.

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

    Re: validation in sheets (excel 2003)

    And you had a very useful extra tip too! (Any idea why Excel sometimes adds quotes around a defined name? I find that very irritating)

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation in sheets (excel 2003)

    Since we are in the topic, how come you can't select more than one columm for the validation. I tried doing it base on the steps you guys gave, but if the validation has one or more columns it gives me an error message. I ended up with doing the concatenate function to have it work. Any ideas?

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

    Re: validation in sheets (excel 2003)

    Do you mean the list containing the allowed values? It wouldn't make sense for that list to have more than one column. Excel must be able to compare the value entered by the user with a single list of values.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validation in sheets (excel 2003)

    I see what you are saying. The only reason I was using two columns was one contain the product number the other the description for that product.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: validation in sheets (excel 2003)

    This could be done with a multicolumn combobox from the control toolbox instead of validation (but most likely would require code)

    Steve

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: validation in sheets (excel 2003)

    Jan Karel,

    and while you're answering Hans' question about why Excel sticks quotes around defined names, I have a similar question or 2:
    - does "defined name" include builtin-in functions (since you're the "name" person by virtue of your Name Manager)?
    - why would Excel stick quotes around a built-in function (regardless of answer to first question)?

    I created a conditional format to color a cell one color if the date in the cell was before today and another color if the cell's date was after today. I thought using conditional formatting was a cinch:
    - use value is in the first drop-down for the first condition, change the second drop-down to less than, type "=TODAY()" without the quotes for the test, and choose my color
    - similar for a second condition to test for greater than.

    I may have originally not typed the = either but I did it again with the = with no difference in outcome.

    When I went to test this, only the first condition worked; cells later than today did not have the color applied. When I checked the conditional formatting, both formulas read as:
    "="TODAY()"" without the outer set of quotes. That is, Excel had added quotes around the BIF.

    Don't ask me why dates earlier than today did work properly.

    Thks.

    Fred

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

    Re: validation in sheets (excel 2003)

    A formula is not the same as a defined name, but Excel treats them more or less the same way in many situations.

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: validation in sheets (excel 2003)

    yes-apparently to the extent of even putting irritating quotes around the function. Let's see if Jan Karel has an explanation.

    Regards,

    Fred

Posting Permissions

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