Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation (2003 SP1)

    Well, now they have gone and done it..... Microsoft has broken one of my favourite features. I cannot get data validation to work when the range is on a different worksheet. I validate for a list, put =rangename, and while the drop down appears as expected the Error alert, although set to stop lets you put in whatever you please.

    This doesn't appear to be a problem if the range is on the same worksheet but I never set up my files like that.

    Does anyone know of a fix or workround cause I use this all the time?

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

    Re: Data Validation (2003 SP1)

    Works fine for me.
    See attached sheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003 SP1)

    Thanks Jan .... now I am really confused .... your worksheet is fine. But mine (see attachment) still doesn't work. I have been looking at this since yesterday and have tried it on three different PCs on different networks with the same result. Any ideas??

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003 SP1)

    It looks like it has to do with your named range. Jan's is $A$1, so absolute on column and row. It doesn't look like your named range is, and in the first cell, it looked like the entire column was highlighted. In fact, every time I went down a row on the 'entry' sheet, the named range moved with me. I think if you redefine 'Fruit' as $A$1:$A$3 it will work for you.

    - Brett

  5. #5
    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: Data Validation (2003 SP1)

    I agree with Brett (BAN) that it doesn't like your defined range. You use the whole column, but you should use only the actual numbers of values to define the range. I assume that you want the named range to "grow" as new items might be added (which is why you used the whole column as the range) so that you don't want to "hardcode" the A1:A3. You can define the range "Fruit" to "refers to":

    <pre>=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A), 1)</pre>


    This is the range starting at Sheet1!$A$1, moving 0 rows down, and 0 columns to the right, that is as long (number of rows) contained in the COUNTA(Sheet1!$A:$A) (it is 3 now, if you add more items to Col A it will increase, if you delete, it will decrease) and 1 column wide.

    [Note: I assume there will be no "blanks" within the list]

    Steve

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

    Re: Data Validation (2003 SP1)

    Steve is spot on. Change your defnition of the named range (e.g. using Steve's advice) and you should be fine.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003 SP1)

    Steve

    How do you handle blanks in a dynamic range?
    I sometimes have used a Worksheet_Change event to sort the list which moves the blank to the bottom.
    Any other (simpler) method of dealing with blanks?

  8. #8
    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: Data Validation (2003 SP1)

    If you want ot include "blanks in your range" and still count correctly, you can use match. Use this as a "refers to:
    <pre>=OFFSET(Sheet1!$A$1,0,0,MATCH(REPT("z",255),S heet1!$A:$A),1)</pre>


    If you want a sorted dynamic list
    If A1:A whatever has the unsorted list (may contain blanks)
    Create named range named : "UnsortedList" and use as the "refers to" the above equation

    add this formula to B1 (this is a formula from Jan Karel <!profile=Pieterse>Pieterse<!/profile> in <post#=257249>post 257249</post#>)
    <pre>=INDEX(UnsortedList,MATCH(ROW()-1,COUNTIF(UnsortedList,"<" &UnsortedList),0))</pre>


    This is an array formula. You will have to decide how large the list might grow to since you need formulas that far.
    Select B1:B10 or B100 or Bwhatever (it should be as large or larger than the size your list might grow to)
    Click Edit (<F2>, then confirm the formula with ctrl-shift-enter)
    You should have a sorted list in col B ending in many #N/A errors

    Create anamed range named : "SortedList" and use as the "refers to":
    <pre>=OFFSET(Sheet1!$A$1,0,1,COUNTA(Sheet1!$A:$A), 1)</pre>


    Now you can use the name "SortedList" wherever you need to refer to the sorted list.

    This list is dynamic and will change as more items are added.

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003 SP1)

    Steve

    Excellent !

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2003 SP1)

    Thank you all .... that did it.

Posting Permissions

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