+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting based on words from criteria list

  1. #1
    Registered User
    Join Date
    09-27-2021
    Location
    netherlands
    MS-Off Ver
    GOOGLE
    Posts
    3

    Conditional formatting based on words from criteria list

    hey!,
    i'm using google spreadsheet and trying to search for words from a criteria list with conditional formating, I only run into 2 problems...

    This works if I'm in the same sheet as the criteria list and the data.
    1.
    =ArrayFormula(REGEXMATCH(A1,join("|",K$6:K$7)))

    but if I want to put the criteria list in another Sheet;
    2.
    =ArrayFormula(REGEXMATCH(A2,join("|",Config!E$2:E$5)))

    Then google conditional formatting gives an error that the formula is incorrect :/ (If I test formula 2 in the data sheet it works, but not if I want to use it as a formula for conditional formatting.)

    how can i use "isblank" in this formula? so that using Config!E:E becomes a bit easier?

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Conditional formatting based on words from criteria list

    in Google Sheets, for some functions that fail when referencing another sheet, these are usually fixed by using the INDIRECT function.

    are you able to provide a link to a demo file (a copy that has sensitive data replaced with demo data)?
    to do so, put the share edit link into a txt file and attach the text file to your post... follow the instructions in the yellow banner at the top of the page here for how to attach a file.

  3. #3
    Registered User
    Join Date
    09-27-2021
    Location
    netherlands
    MS-Off Ver
    GOOGLE
    Posts
    3

    Re: Conditional formatting based on words from criteria list

    Thank you for letting me know about the INDIRECT function. didn't know it existed (The part of the criteria list is now working.
    =ArrayFormula(REGEXMATCH(A3,join("|",INDIRECT("Config!A$2:A$5"))))

    But the second question, how can i add in this formula the option to use only fields from the criteria list, that are populated with data.

    =ArrayFormula(REGEXMATCH(A3,join("|",INDIRECT("Config!A$2:A$5"))))
    Attached Files Attached Files
    Last edited by kingkai88; 09-28-2021 at 03:29 AM.

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Conditional formatting based on words from criteria list

    to allow you to add more items to the list on the Config sheet, use this formula for your Conditional Formatting:
    Please Login or Register  to view this content.

    EDIT, corrected the formula to suit the current range.


    because you have 2 header rows, you should change the "Apply to range" to be:

    Please Login or Register  to view this content.
    and then use the following Conditional Formatting on the range:

    Please Login or Register  to view this content.
    if you do not make the changes, then if you put anything into those header cells that has a match, it will change colour.
    Last edited by janmorris; 09-28-2021 at 06:44 AM.

+ 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. Conditional Formatting entire row based on a list of criteria
    By Ashleytaylor1702 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2021, 10:23 AM
  2. Conditional Formatting Bold based on words
    By onlysubime in forum Excel General
    Replies: 2
    Last Post: 02-27-2021, 09:57 PM
  3. Replies: 3
    Last Post: 01-13-2018, 02:36 PM
  4. Replies: 2
    Last Post: 07-26-2016, 02:05 PM
  5. [SOLVED] Conditional Formatting of List based on selections from Dropdown list
    By FoxyPinkChick in forum Excel General
    Replies: 4
    Last Post: 05-31-2013, 03:32 AM
  6. conditional formatting using words but exact words
    By bopsgtir in forum Excel General
    Replies: 5
    Last Post: 12-02-2010, 02:51 PM
  7. Conditional Formatting and list of Criteria
    By maurices5000 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-19-2007, 09:34 AM

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