+ Reply to Thread
Results 1 to 7 of 7

Google Sheets - Tricky conditional formatting with two requirements

  1. #1
    Registered User
    Join Date
    07-18-2017
    Location
    Ph
    MS-Off Ver
    2010
    Posts
    3

    Google Sheets - Tricky conditional formatting with two requirements

    Hi!

    I am stuck on making this formula for conditional formatting to work:
    =AND(search("Buymaxx*",B:B),COUNTIFS(F:F,"=891",F:F,"=7410",F:F,"=318",F:F,"=277",F:F,"=983",F:F,"=386",F:F,"=522",F:F,"=802",F:F,"=895 (buffer truck)")>1)

    EDIT: I also tried this formula but to no avail =AND(search("Buymaxx",B:B),COUNTIF(F:F,F:F)>1)



    Basically, I want the sheet to highlight cells in column F that has different plate numbers (the numbers on the formula) if column B contains the word "Buymaxx". I am trying this on google sheets but I think it is still appropriate to post this on this forum. I hope you can help me on this.

    Thanks in advance!

    Ian
    Last edited by AliGW; 07-19-2017 at 12:45 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,386

    Re: Tricky conditional formatting with two requirements

    Thread moved to the other platforms section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Google Sheets - Tricky conditional formatting with two requirements

    dont know what buffet truck is meant to do but

    if you apply formula to range F1:F1000

    formula would be
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    07-18-2017
    Location
    Ph
    MS-Off Ver
    2010
    Posts
    3

    Re: Google Sheets - Tricky conditional formatting with two requirements

    Hi. Thanks for your reply. Buffer truck is just a text added so that users now its the number of the spare truck. I tried your suggestion and used 891 and 7410 as the truck assignments but the formula didn't work. I think Google sheets treat conditional formatting with cell names such as F1 and B1 as absolute and won't automatically apply to the rest of the cells that's why I use the column names when creating custom conditional formats

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Google Sheets - Tricky conditional formatting with two requirements

    hmmmm
    seems to work in my example
    i added buffer truck as well

    here the share file
    https://docs.google.com/spreadsheets...it?usp=sharing

  6. #6
    Registered User
    Join Date
    07-18-2017
    Location
    Ph
    MS-Off Ver
    2010
    Posts
    3

    Re: Google Sheets - Tricky conditional formatting with two requirements

    Quote Originally Posted by humdingaling View Post
    hmmmm
    seems to work in my example
    i added buffer truck as well

    here the share file
    https://docs.google.com/spreadsheets...it?usp=sharing
    Hi.

    Thanks for your reply! Will it be possible if say, the name was Buymaxx Cali and Buymaxx Store and the plate numbers will highlight if they are different for each?

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Google Sheets - Tricky conditional formatting with two requirements

    if you want different rules/highlights, you need to add additional rules

    i suggest if your conditional formatting rules get "too tricky"....put a helper column with the formula in
    and then simply apply conditional format on the the helper column

    you can hide the helper column if you dont want it to show

+ 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. Tricky Conditional Formatting
    By avannoord in forum Excel General
    Replies: 3
    Last Post: 06-27-2017, 09:37 AM
  2. Conditional formatting if the complicated requirements are met
    By gesaugen in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-28-2015, 07:13 AM
  3. A tricky Conditional Formatting question...
    By mindfire in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-26-2015, 08:34 AM
  4. Tricky conditional formatting
    By ZoharFurman in forum Excel General
    Replies: 1
    Last Post: 10-27-2013, 12:49 PM
  5. [SOLVED] Conditional Formatting leaving cell blank if doesn't meet requirements
    By Rhiannon25 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-11-2013, 11:55 PM
  6. [SOLVED] tricky conditional formatting!
    By wannabe_guru in forum Excel General
    Replies: 14
    Last Post: 05-07-2012, 09:34 AM
  7. tricky Advanced Conditional Formatting
    By rajbloggs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2009, 08:08 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