Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation Problem (2003 SP2)

    Good afternnon

    In response to a request for adding a name to a staff list Steve, in this post <post#=724,757>post 724,757</post#>, gave me an excellent solution, because however it added a line below each department it altered many other things in this particular workbook which caused it not to work. I have though already found a very good use for it in some new projects.

    I have decided to use a less elegant method but I have run into a problem that I hope somebody can assist with, as an example

    1. I have set up a list of names in A1:A10
    2. In A11 I have this: =IF(OR($D$1="",COUNTIF($A$1:A10,$D$1)),"x",$D$1) and have dragged it down to A20, all cells A11-A20 show the formala
    3. in D1 I have defined a name range of Staff and in the refers to field have put: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"<>x" ),1)
    4. I have then clicked the Sheet1 tab and entered the following

    Private Sub Worksheet_Calculate( )

    On Error Resume Next

    Application.EnableEvents = False

    Range("Staff") = Range("Staff").Value

    Application.EnableEvents = True

    On Error GoTo 0

    End Sub

    5. I have saved the WB as StaffExamples

    If I now select cell D1 and type in a new name it appears in A11 and is added to the list in D1, however the formula in A12:A20 now disappears

    I have attached a sample WB that goes to stage 5 above so that you can see that prior to adding a new name in D1 the formula is still showing in the relevant cells but goes when a new name is added.

    Can anybody see what I have done wrong?

    Cheers

    Steve

    Editted 14:58 as wrong range name shown
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Data Validation Problem (2003 SP2)

    If I enter Liam in D1, it also appears in A11 and the code replaces the formula in A11 with its value, but the formulas in A12:A20 remain.
    If I then enter Michael in D1, it also appears in A12 and the code replaces the formula in A12 with its value, but the formulas in A13:A20 remain.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Problem (2003 SP2)

    Hi Hans

    Thanks for the response

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> How very weird, I have downloaded my own workbook and tried it again but for me it still behaves the same, I have tried exiting the cell D1 by using enter and by using tab but it always the same result, Liam is added to A11 and to the range but then the formula disappears in A12:A20. I will try it on my home PC later and see if it works there otherwise I am at a loss

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Data Validation Problem (2003 SP2)

    I'm using Excel 2002 at the moment, but I would be surprised if Excel 2003 behaved differently in this respect.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Problem (2003 SP2)

    Hi Hans

    Thanks as usual, I am at a loss to understand what is going on here, I have a new laptop at home and have re-created the workbook from scratch and used the original workbook I posted but I still get the same results, 1 entry into D1 and then the formulas in A11:A20 have gone. I thought perhaps it was to do with my settings on the work machine but on my new laptop the Office 2003 is a completly new installation.

    Below shows the version that I am using but I can't understand what has gone wrong, thanks though for your input

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Data Validation Problem (2003 SP2)

    I won't be able to test on Excel 2003 until next Monday, but perhaps someone else can test it in the meantime...

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Problem (2003 SP2)

    I don't have 2003, Like Hans I am using 2002. I can confirm though that it works for me in the same way as Hans first post. My work pc is 2003 (I think), if no-one responds sooner, i will ckeck tomorrow.

    Regards

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Problem (2003 SP2)

    Thanks To both of you

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Data Validation Problem (2003 SP2)

    I can replicate that in 2003. The issue is in the range definition where 2003 counts blank cells as not being equal to 'x', so your range is much bigger than you expect. You can either change the range definition to something like:
    <code>=OFFSET(Sheet1!$A$1,0,0,SUMPRODUCT((Sheet1!$ A$1:$A$10000<>"x")*(Sheet1!$A$1:$A$10000<>"")),1)</code>
    or, since you are using code anyway, you could reassign the range in code.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Data Validation Problem (2003 SP2)

    In Excel 2002, COUNTIF operates on the intersection of the range specified in the first argument and the used range of the sheet.
    In Excel 2003, COUNTIF ignores the used range.
    Hence the difference.
    I had forgotten about this, but I found an old thread about it - see <post:=557,545>post 557,545</post:>.

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Problem (2003 SP2)

    Thanks Rory

    For the interim period the formula is great, as I am (very slowly) trying to learn code I will try and experiment with it

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Problem (2003 SP2)

    Thanks for the link Hans

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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