How can I incorporate Excel Array formula in Conditional Formatting?

0
votes

I need some help in setting up my conditional formatting. Any help or feedbacks will be greatly appreciated! This is my scenario:

There is a table named WkdayPHols where it contains a specific holiday to a respective date.

enter image description here

On another sheet within the same workbook, I need to highlight the specific cell with this condition using conditional formatting:

  • If there is a matching state (in Column C), with a matching Date (Row 7), indicate that cell as 1 - as shown
  • Row 9 Yellow cell example - Cell C9 = VIC, Cell D7 = Date Value 43405 - no match, therefore #N/A
  • Green cell example, Date Value 43410 is found to be a holiday in WkdayPHols, any rows with Vic in Column C will have 1.

I have achieved that by using the array formula - as shown in the formula bar. However, I only want this condition to be presented as part of conditional formatting, by highlighting the specific cell with the given criteria (=1). But array formulas is not supported in conditional formatting.

I need the all the cells to be blank, without formulas for actual inputs.

Any solution/suggestion?

enter image description here

1
excelexcel-formulaconditional-formattingarray-formulas
try using =SUMPRODUCT(<formula>)... – Rosetta
@Rosetta I tried using that and it didn't work, value=1 only appears if it is in an array form – Skyler

1 Answers

1
votes

Change the formula in the cells to,

=IFERROR(AGGREGATE(15, 7, WkdayPHols[column1]/((WkdayPHols[dates]=I$7)*(WkdayPHols[state]=$C9)), 1), TEXT(,))

Use Fill Right and Fill Down, do not drag that formula into adjacent cells or the table columns will shift.

Change the cells' format to a custom number format of,

;;;

Create a conditional formattting rule based on,

=isnumber(d9)