+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting from List and no blank cells

  1. #1
    Registered User
    Join Date
    11-10-2015
    Location
    Östersund, Sweden
    MS-Off Ver
    Office for Mac
    Posts
    7

    Conditional formatting from List and no blank cells

    Hi all,
    I'm trying to acheive Data Validation from a named range (ListName) along with not allowing empty/blank cells.
    Cells in the spreadsheet by default consists of information alternative a hyphen (-) which is part of a List of items that are allowed.
    But if someone deletes the content in a cell I need them to choose at least "-" if not one of the other allowed items in the list.

    Initially I had the following as Data Validation:
    =ListName

    I found a thread which should force content in a cell (blanks not allowed):
    =COUNTIF($B7:$NH200, "")=0

    But I haven't been able to work out if it's possible to combine the two.

    Hope it all makes sense.

    Thanks in advance for any ideas.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Conditional formatting from List and no blank cells

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Conditional formatting from List and no blank cells

    Not really clear what you need. It might be as simple as unticking "ignore blank" in Data Validation. If not, then I need more info.

  4. #4
    Registered User
    Join Date
    11-10-2015
    Location
    Östersund, Sweden
    MS-Off Ver
    Office for Mac
    Posts
    7

    Re: Conditional formatting from List and no blank cells

    Thanks for your responses. I tried uploading a sample file both in Safari and Chrome but the attachment tool won't load in either.

    Thanks any way.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Conditional formatting from List and no blank cells

    READ the yellow banner (top). It does not even mention the attachment icon.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 01-01-2021, 05:39 PM
  2. Conditional formatting, based on non-blank cell from list of cells
    By rayted in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2020, 08:01 AM
  3. Conditional Formatting - Color Formatting for Blank Cells
    By mlbdc2012 in forum Excel General
    Replies: 10
    Last Post: 02-10-2015, 06:28 AM
  4. Replies: 3
    Last Post: 02-09-2015, 09:00 AM
  5. Conditional Formatting. No formatting for Blank Cells
    By missellyt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-11-2014, 06:42 AM
  6. Replies: 5
    Last Post: 05-18-2013, 07:49 PM
  7. [SOLVED] 3 Outcomes Conditional Formatting based on 4 blank/non blank cells
    By Supraman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2012, 06:21 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1