Thanks Thanks:  0
Needs Pictures Needs Pictures:  0
Picture(s) thanks Picture(s) thanks:  0
Results 1 to 14 of 14
  1. #1
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default Question on Conditional Formatting Excel 2016

    Ok, I give in. In the days of XP/Office 2003 I was a bit of a whiz with Excel, but it seems to be a little more complex than it needs to be in the 2016 version.

    In particular, Conditional Formatting...which used to make so much sense....doesn't make any sense or work intuitively any more, so I need a little help please....

    This is what I have:
    Capture.JPG

    Now there are several of those black cells on the left that should obey those rules, but none of them do. Have tried various other illogical combos.....nup. They are all within the correct rows range of 11 to 39.

    And what does "Stop if True" mean?

    (I will have a follow-up question on how to get another cell in the row to follow the same formatting - from the value in "S", but let's wait on that.....)
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  2. # ADS
    Google Adsense Advertisement
    Join Date
    Always
    Location
    Advertising world
    Posts
    Many





     
  3. #2
    Join Date
    Feb 2016
    Location
    Perth WA Australia
    Posts
    828

    Default

    I think your issue is your conditional formatting is text rather than number, note the quotation marks.

    Stop if true is exactly that, it'll stop if other conditional formatting holds true. I've never had a need to use it, but its commonly used when you want to hide some conditional formatting when another criteria holds true.

    In terms of getting another cell to follow the same use the format painter, which can be found under the home tab, directly below the "home" text, it looks like a paint brush

  4. #3
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    If I leave only one rule for the first cell it still doesn't work. It used to be that it would follow those conditions down the list, and I thought it still did that.
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  5. #4
    Join Date
    Jun 2015
    Location
    Mexico. Actual Mexico not Victoria.
    Posts
    418

    Default

    Is it because you have a formula as opposed to a cell value?

  6. #5
    Join Date
    Feb 2016
    Location
    Perth WA Australia
    Posts
    828

    Default

    Quote Originally Posted by FenceFurniture View Post
    If I leave only one rule for the first cell it still doesn't work. It used to be that it would follow those conditions down the list, and I thought it still did that.

    Yes still does that, i've tested my original theory and edited my first comment.

    I think its because your conditional formatting is actually looking for ">=90" rather than looking for a number greater than 90 if that makes sense.

  7. #6
    Join Date
    Feb 2016
    Location
    Perth WA Australia
    Posts
    828

    Default

    Change all your conditional formatting using this format and it'll work fine

    Conditionalformatting.png

  8. #7
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    Harrrr....while you were posting I got to the same conclusion:
    Capture.JPG

    and she works!

    I got to that by adding three "Greater than" rules, and then edited rules and that is what they look like now. I suspect that I'll be able to bumble through that other question myself (to format other cells on condition of another).

    Thanks for your help!
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  9. #8
    Join Date
    Feb 2016
    Location
    Perth WA Australia
    Posts
    828

    Default

    no worries... sadly i spend more time in Excel than i do in the shed...

  10. #9
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    I used to too....

    Perhaps another question then please?

    If I have a cell with a value of "1,2,5,6" can you think of a formula that will count how many commas there are in that? Nothing I could find in the list.....

    I could write a Function for it but Excel is very much "use it or lose it"...and I've lost it. (My old Personal.xls seems to have disappeared, and I used to be able to find previous examples of code I had written, and then it all came back to me)
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  11. #10
    Join Date
    Mar 2018
    Location
    Sydney
    Posts
    469

    Default

    Quote Originally Posted by FenceFurniture View Post
    I used to too....

    Perhaps another question then please?

    If I have a cell with a value of "1,2,5,6" can you think of a formula that will count how many commas there are in that? Nothing I could find in the list.....

    I could write a Function for it but Excel is very much "use it or lose it"...and I've lost it. (My old Personal.xls seems to have disappeared, and I used to be able to find previous examples of code I had written, and then it all came back to me)
    To count, use a formula to replace the comma with nothing and count it's length (using len).

    The difference between the original length and this is the number of commas.

    Regards.

    Adam

    Sent from my SM-G950F using Tapatalk

  12. #11
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    Quote Originally Posted by taz01 View Post
    To count, use a formula to replace the comma with nothing and count it's length (using len).
    Yes, like the logic Adam, but can't find a formula that does a replace of the commas (maybe I'm having a brain fart)....
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  13. #12
    Join Date
    Mar 2018
    Location
    Sydney
    Posts
    469

    Default

    Hi FF,

    Use "substitute".

    The full formula would be something like =LEN(cell) - LEN(SUBSTITUTE(cell, "," , "")

    Regards

    Adam

    Sent from my SM-G950F using Tapatalk

  14. #13
    FenceFurniture's Avatar
    FenceFurniture is offline The prize lies beneath - hidden in full view
    Join Date
    Oct 2010
    Location
    1017m up in Katoomba, NSW
    Posts
    10,649

    Default

    Ahhhh, lovely Adam, thank you. As usual the question box in insert function is not very helpful - "Find and Replace" gets a response of "Please rephrase your question".....when Excel knows very very well what Ctrl F is for.....
    Regards, FenceFurniture

    COLT DRILLS GROUP BUY
    Jan-Feb 2019 Click to send me an email

  15. #14
    Join Date
    Mar 2018
    Location
    Sydney
    Posts
    469

    Default

    Glad to help.

    I gave up using excel help a while ago, Microsoft doesn't write it's help guides in clear English. Google is awesome for problems like this.

    That particular one i needed for clearing some payroll data (changing the order of surname firstname) for a project i was helping on.

    Regards.

    Adam

    Sent from my SM-G950F using Tapatalk

Similar Threads

  1. Excel Question
    By Chris Parks in forum COMPUTERS
    Replies: 5
    Last Post: 30th July 2011, 12:03 AM
  2. Tankstand needs Excel help
    By Tankstand in forum COMPUTERS
    Replies: 5
    Last Post: 3rd October 2008, 12:21 PM
  3. MS excel
    By Harry72 in forum COMPUTERS
    Replies: 4
    Last Post: 30th July 2008, 12:36 AM
  4. Excel question
    By Daddles in forum COMPUTERS
    Replies: 26
    Last Post: 6th June 2007, 12:29 AM

Posting Permissions

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