Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2006
    Location
    Rio Rancho, New Mexico, USA
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Cool Data validation with a named range and conditional formatting

    I'm working with someone who has a spreadsheet using a named range for data entry validation. It accepts blanks but it should not. WHen I change this to a list, it doesn't accept the blanks and that's what she wants but she wants to use the named range which I can't get to work.

    Then there are other fields on the spreadsheet which should have something entered into them but which some folks leave blank. I've tried like crazy to code a conditional format to turn a cell red when something is expected there but the cell is blank. I can get it to work for one cell but not for the entire range. My condition is, in English, if cell a2 is not blank and cell g5 is blank, turn g5 red. The same thing is true for a3 and g3, a4 and g4, etc. Can't get it to work.

    A nudge in the right direction is all I think I need.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 85 Times in 81 Posts
    Do you have blank cells in the named range? Turn off the Ignore blanks option in the data validation

    for the CF, select all the cells in column G that you want and the formula is (assuming G2 is the active cell)
    =AND($A2<>"",$G2="")
    note there are no $ signs in front of the row numbers (they are not strictly necessary in front of the column letters here)
    Regards,
    Rory
    Microsoft MVP - Excel.

  3. #3
    Lounger
    Join Date
    Aug 2006
    Location
    Rio Rancho, New Mexico, USA
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks! I had put a space between the quotation marks. Should have been null!

    Thank you so much!

Posting Permissions

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