2

I'm trying to extend user0's beneficial formula to more than 3 sheets, but Google Sheets reports an error in the Conditional Formatting custom formula field

Google Sheet complains Invalid formula

From this:

=(COUNTIF(Q:Q,Q:Q)>1)+(REGEXMATCH(Q1,TEXTJOIN("|",1,UNIQUE({INDIRECT("Sheet2!Q2:Q");INDIRECT("Sheet3!Q2:Q")}))))

to this

=(COUNTIF(Q:Q,Q:Q)>1)+(REGEXMATCH(Q1,TEXTJOIN("|",1,UNIQUE({INDIRECT("Sheet2!Q2:Q");INDIRECT("Sheet3!Q2:Q");INDIRECT("Sheet4!Q2:Q")}))))

I will be using the conditional formatting in every sheet to highlight duplicates, not just one. It would be nice to be able to add and remove sheets in the future without having to go back and edit every existing conditional formatting formula to accommodate the change.

I have shared a copy of my spreadsheet here

2
  • Welcome to Web Applications. From the question "but Google Sheets reports an error in the Conditional Formatting custom formula field". Please add the textual error and enough details in order to allow others to reproduce the error. This should include sample data as well a the a clear description of the expected result for the sample data. Commented Feb 6, 2023 at 21:35
  • Thanks for adding the image. The question still doesn't include enough details. Tip: You should assure that all the functions that returns an array in formula, returns arrays of the same size. When using open references this might be tricky... as each sheet should be reviewed and inserting rows in one sheet might brake the formula. Commented Feb 6, 2023 at 22:02

1 Answer 1

1

FORMULA 1

The original answer didn't work for you because in testing, you were using partial email addresses and the REGEXMATCH function was returning unintended string matches (email1 = email10). In practice, full email addresses would be unlikely to trip this ([email protected][email protected]). Kudos to you for finding the error path, an art unto itself. 👍

I tightened up the formula by enclosing all strings in additional unique characters to avoid that partial-match problem.

=(COUNTIF(Q:Q,Q:Q)>1)+(REGEXMATCH("➡️"& Q1 &"⬅️",
             "➡️"& TEXTJOIN("⬅️|➡️",1,UNIQUE(
             { INDIRECT("Sheet2!Q2:Q");
               INDIRECT("Sheet3!Q2:Q");
               INDIRECT("Sheet4!Q2:Q") } ))&"⬅️"))

Notes

  1. In my example I used ➡️<string>⬅️ for stylistic purposes. Almost any additional opening and closing characters that serve to delimit the strings would achieve the same objective. They can match or not: ⬆️<string>⬆️, Z<string>ZZ, ✅<string>XYZ, and so forth. To use a " as the delimiter, use CHAR(34) instead.
  2. In the Conditional format rules editor, the beginning of the range in Apply to range must match the cell whose condition is being tested in the Custom formula. e.g. If your Apply to range is Q1:Q1000, then REGEXMATCH("➡️"& Q1 &"⬅️"; if it is Q3:Q1000 then REGEXMATCH("➡️"& Q3 &"⬅️" If these are mismatched, you will have issues where formatting is applied to the wrong row (OFFSET).
  3. The remaining arrays do not need to match. They do not need to be the same column, and they do not need to be the same length. They do need to be a single column however that can be adjusted simply by combining columns using FLATTEN.
     
    For example, if you have 2 columns (Q2:Q and R2:R) of emails in Sheet3 you could modify the array as follows:
    { INDIRECT("Sheet2!Q2:Q");
      FLATTEN(INDIRECT("Sheet3!Q2:R");
      INDIRECT("Sheet4!Q2:Q") }

 


FORMULA 2

Your requirements diverge materially from the question that prompted the original solution.

  1. You want to be able to add multiple sheets without having to constantly update the conditional formatting formula (dynamic)
  2. You are including the conditional formatting on every sheet whereas the other solution is targeted at a single sheet

A modified conditional formatting formula in concert with 2 helper columns located somewhere in your workbook addresses both these needs. For illustration, in your linked workbook, I created an additional sheet named helper to hold the two columns. An additional upside is that the old solution converted the arrays to text which has limitations should you wish to process a large number of entries.

Conditional Formatting

=IFERROR(IF(MATCH(Q3,INDIRECT("helper!_emails"),0),TRUE,))

Helper Column helper!C2

=SORT(UNIQUE(LAMBDA(x, FILTER(x, COUNTIF(x,x)>1))
   ( 
     FLATTEN(MAP(FILTER(_ranges,_ranges<>""), LAMBDA(r,
                 TRANSPOSE(INDIRECT(r)))))
   )
))
Name Range Description
_emails helper!C:C List of all duplicated emails
_ranges helper!A4:A List of ranges in which to search for duplicates

Notes

  1. Basically I turned this thing on its head and instead generate a list of duplicate emails in helper!C2:C based on the ranges you list in helper!A4:A. Now your conditional formatting just needs to check if the email address in question shows up in that list. If so... "duplicate".
  2. You can add and remove ranges from helper!A4:A and they will be dynamically included in the search for duplicates.
  3. Once you have added conditional formatting in a sheet, you do not need to modify it should you add or remove ranges/sheets. The basic formula is identical from sheet to sheet except for the search cell and the column (although they could be identical as well)

Google Sheets Functions

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.