Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    This topic is about the spreadsheet that I have attached to Post 792073

    I have created a new thread as the original poster has not yet responded and I don't want to cause any confusion on their first post in the lounge.

    The spreadsheet uses identical conditional formatting in cells F6 to H16, but it behaves differently in some of the cells.

    If I enter random text (such as "aa") into the cells then some cells display the validation stop message (G7:G12, G14:G16, H6, H13). All the other cells allow me to enter this text, even though it is not in the validation list.

    I cannot see any difference in the cells that would account for this difference in behaviour.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Appears to be the "Data Validation Formula" was copied across and absolute wasn't applied.

    So Col E & F work fine, but Column G now refers to Column F and Column H refers to Column G.

    That's if I understand correctly.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='stans' post='792317' date='06-Sep-2009 14:00']Appears to be the "Data Validation Formula" was copied across and absolute wasn't applied.

    So Col E & F work fine, but Column G now refers to Column F and Column H refers to Column G.

    That's if I understand correctly.[/quote]

    This is not the problem, column G is meant to refer to column F, and H to G. That was intentional.
    What seems to be happening is that the validation drop down list always looks right, but Excel allows me to type invald content into the cells IF the cell to the left is empty, but if I have "aa" or something else invalid in the cell to the left then I get the Validation Stop error.

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

    I can see your problem as stated, but don't understand why the error only populates in the left hand blank cell. If you enter an invalid item into the cells from right to left (for example), then the "stop" error fails until the left hand blank cell, at which point the stop works.

    I can't see any logic as to why.

    I'm wondering if an active cell checker in the change event might be more robust, leaving the data validation in place, but without the stop error being active. ??

  5. #5
    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 uncheck the Ignore Blank option. I'm not sure exactly what the underlying cause is (it can also cause issues with named range validation), but my general rule is to always uncheck that option.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='rory' post='792407' date='07-Sep-2009 09:33']You need to uncheck the Ignore Blank option. I'm not sure exactly what the underlying cause is (it can also cause issues with named range validation), but my general rule is to always uncheck that option.[/quote]

    Thank you, I would never have guessed that one.

Posting Permissions

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