Conditional Format a Cell based on a sub-array formula

Question

All help greatly appreciated.

I have a workbook which holds all details of employees on the main worksheet in an array.  These employees have qualifications which expire and I have been able to apply conditional formatting to highlight when the qualifications are due to, or have expired.  (This all works fine).

From there I have additional worksheets with sub-arrays from the main array, e.g. all British employees to one sheet, European to another etc.  It is in these sheets that the conditional formatting is not working.  I'm assuming this is down to the cells being formulas.

Can anyone please advise me on what I need to do to apply the conditional formatting for the additional worksheets.

I appreciate your time and expertise.

Kind Regards

Answer 1

Hi Bob,

I have solved it.  I very silly mistake by myself.

Where I have the conditional formatting in the additional sheets, I seem to had missed out the 'applies to' section.  I only had the conditional formatting covering one cell and not the full column.

My apologies for waisting your time but I'm very grateful for your help.

Best wishes

Answer 2

Yes that is correct.  The long formula produces/pulls a date from the master sheet.  I would like these dates to follow the same conditional formatting as in the master sheet where they are highlighted yellow if within 30 days of now or red if expired.

Sorry for the confusion, I hope this is more clear.

Answer 3

I'm still not sure I'm following. So the cell with the formula you show above has conditional formulas which look like cell value=now()+30 Is this correct? If so, does this formula produce a date? I think you want the conditional formats in this cell to refer to cells in the master sheet.

Answer 4

Hi Bob,

Ok, so I have a worksheet with my master array and in that array I have three conditional formats which highlight a cell based on the date.

The first CF is             'Cell Value =NOW()+30', make cell yellow

the second                 'Cell Value =NOW()', make cell red

and the third                'formula =ISBLANK(E4)=TRUE' (Stop If True), to not format cell.

This all works perfect.

In my workbook I have additional worksheets that pull rows from the master array on a given criteria e.g. British nationality.  The formula I have used for doing this is

{=IF(ROWS(A$4:A4)>$AK$1,"",INDEX(Overlook!A$4:A$1000,SMALL(IF(ZealGMS.xlsx!Nationality="British",ROW(ZealGMS.xlsx!Nationality)-ROW(Overlook!$AI$4)+1),ROWS(A$4:A4))))}

This formula also works perfect and all the correct rows are pulled and placed in the correct worksheet.

The problem I'm having is in these additional worksheets.  The conditional formatting is not highlighting the dates pulled from the master array.  I have used the same conditional formatting rules as with the main worksheet so I'm not sure where the problem lays.

Thanks again for your time

Answer 5

I have no idea what is wrong, because I have no idea what you have tried, what the conditional formula is, nor what the data looks like. I can assure you it has nothing to do with the cells containing formulas. The problem is clearly in your conditional formula. Post some details and specific examples and maybe we can help.