0

I am looking for a conditional formatting formula that validates two conditions from another sheet.

I have Sheet1 and Sheet2: Sheet2 has daily records of workers such as time in and off, Sheet2 contains a list of all workers and certain dates.

I would like to find a way to color cells in Sheet1 IF 1) there is a record of a specific worker AND 2) it has the same date in the Sheet2.

Example: C4 colored orange because a record of B4 (Worker A) and C2 (11/1/2023) exist in Sheet2 (B4); C5 colored is not colored because there is no record of Worker B on 11/1/2023 in Sheet2.

enter image description here

enter image description here

Improve this question
0

3 Answers 3

Reset to default
2

The best option here is to restructure the data on Sheet2.

Put the date in a column to the left of the Name. Like this:

enter image description here

If you do that, and supposing the Sheet2 were called Sheet2New, the formula for your conditional formatting rule would be some variant of (edit the range addresses as necessary):

=NOT(ISNA(XMATCH(C$2&$B4,Sheet2New!$B$3:$B$6&Sheet2New!$C$3:$C$6)))

For example:

enter image description here

For Google Sheets, the formula will be like this:

=NOT(ISNA(MATCH(C$2&$B4,ARRAYFORMULA(INDIRECT("Sheet2New!$B$3:$B$6")&INDIRECT("Sheet2New!$C$3:$C$6")),0)))

If you can have the data from Sheet2 on the same sheet as the conditional formatting, you don't need to use INDIRECT.

Improve this answer
2
  • Thank you! It worked in Excel, but not in Google Sheets. Would it be another formula for the same conditional formatting?
    – Louis Cerato
    Nov 4 at 20:12
  • 1
    I've added a formula for Google Sheets. Please let me know if it works.
    – FlexYourData
    Nov 4 at 23:59
1

Since the data is not properly structured as it should be hence, you have two options, either to restructure the data or else use two helper columns in Sheet2 so that the Conditional Formatting can be applied in the required areas of Sheet1.

I am bit late as FlexYourData Sir has given you the right way on restructuring the data and using Conditional Formatting, hence I'm opting out for the second alternative i.e. using two helper columns in Sheet2, which is also possible to do.

As I am not sure about your Excel Version and assuming there is no Excel Constraints as per the tags posted, then you could try the following.

enter image description here

As it can be seen two helper columns has been added on Sheet2 far right, and applied the following formulas:

• Formula used in cell J3

=SCAN(0,B3:B9,LAMBDA(x,y,IF(ISTEXT(y),x,y)))

&,

• Formula used in cell K3

=SCAN(0,B3:B9,LAMBDA(x,y,IF(ISNUMBER(y),"",y)))

In Sheet1 select the range C4:I8 hit ALT+H+L+N which opens the New Formatting Rule, Select the last rule --> Use A Formula To Determine Which Cells To Format and enter the following formula in the rule description.

=XMATCH($B4&"|"&C$2,Sheet2!$K$3#&"|"&Sheet2!$J$3#)

Select preferred formatting fill color from the tabs and press OK twice.

If you are not using MS365 then you need to use the following formulas in place of those above in Sheet2

enter image description here

• Formula used in cell J3

=IF(ISTEXT(B3),J2,B3)

&,

• Formula used in cell K3

=IF(ISNUMBER(B3),"",B3)

Bothe above formulas needs to be filled down!.

In Sheet1 in Conditional Formatting Rule.

=MATCH($B4&"|"&C$2,Sheet2!$K$3:$K$9&"|"&Sheet2!$J$3:$J$9,0)

All the steps for using Conditional Formatting remains same except the formulas are different. Please note to change the cell ranges and references as per your suit. Also, it is highly suggested to follow proper data restructuring.

In Google-Sheets the same formulas worked without needing to have an additional helper columns:

The formulas needs to be placed in Sheet1 and needs to be filled down and filled right:

enter image description here

=IFERROR(ARRAYFORMULA(XMATCH($B4&"|"&TEXT(C$2,"mmddyyyy"),
 SCAN(0,Sheet2!$B$3:$B$9,LAMBDA(x,y,if(ISNUMBER(y),"",y)))&"|"&
 TEXT(SCAN(0,Sheet2!$B$3:$B$9,LAMBDA(x,y,if(ISTEXT(y),x,y))),"mmddyyyy"))),"")
  • To set the Conditional Formatting Select the range C4:I8
  • Goto Format Tab & select Conditional Formatting
  • On selecting it opens a panel on the right hand side of the sheets, under Format Rules select Greater Than and enter 0 next.
  • In the Formatting Style choose both Fill & Text Color as same and press Done. That should work on your end.
  • Additionally, since the sheet is formulated, you can make the sheet Protected so that no one make any changes to the formulas to mess up the algorithms.

enter image description here

Improve this answer
4
  • Thank you! :) Structuring data would be an easier task in my case :).
    – Louis Cerato
    Nov 4 at 20:14
  • I had a SCAN solution as well, but you can't use LAMBDA functions in conditional formatting rule.
    – FlexYourData
    Nov 4 at 23:45
  • 1
    @FlexYourData Sir, I have not use the LAMBDA() function in CF I have used XMATCH() Function instead, but used the SCAN() in helper columns! You can refer in answer!
    – Mayukh Bhattacharya
    Nov 4 at 23:46
  • 1
    Ah yes, I see it now. Nice solution 👍
    – FlexYourData
    Nov 6 at 15:54
0

Another solution, this time without restructuring data.
You should define a name (in Name Manager) and call it e.g. result.
Then you will define a rule for conditional formatting:
=result
The formula is shown in the screenshot below. Formulas in the sheet in conditional format area have been enetered only for illustration.
Solution not tested in Google Sheets.
Workers List

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .