Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unhappy Data Validation - CUSTOM v2007 RESTRICT EXCEEDING A TOTAL

    Hi all,

    Have attached sample file to show my problem. I have set a custom Data Validation rule on cells f2:f20 which should restrict the total of all entries in that range to <=1,000,000. For some reason it lets me enter numbers in some cells but others it gives me the retry message. If you look at where I have done 'invalid data circles' it has circled some and not others, and yet the list still does'nt exceed the 1,000,000???

    I have tested this by trying to put in numbers as little as single digit, or large numbers but i seem to keep getting the retry spasmodically. Can anyone please shed some light on what the logic is here, or what I've done incorrectly?

    I copied the example from the "Contextures" site:
    http://www.contextures.com/xlDataVal07.html#Limit

    Many thanks everyone.
    Attached Files Attached Files
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

  2. #2
    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
    You need to make the formula references absolute - in other words, change this:
    =SUM(F2:F20)<1000000
    to this:
    =SUM($F$2:$F$20)<1000000
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many thanks Rory, I REALLY appreciate your prompt response, I obviously didn't follow the instructions acurately! All the best.
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

Posting Permissions

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