Multiple condition Formula Help.

[craigs23]

I am in the process of using the Index & Register commands to build a summary sheet fro a much larger database.　

I have got this portion sorted no problems, however what I am trying to do is to use conditional formatting in conjunction with these cells to　

highlight time constrained tasks / deadlines and upcoming deadlines.　

The formulas I am trying to use are as follows:　

=OR(AND(J36="",TODAY()-H27>15),AND(J36<>"",J36-H27))　

Where:　

H27 = Start date 　

J36 = Completion date (if entered)　

So what I am trying to do is to highlight J36 Red if the cell is empty and todays date is >15 days after the date in H27 or J36 has a date entered which is > 15 days after H27.　

I also have 2 further conditions which should highlight the cells yellow or green (both variations on the formula above)　

This works OK if the cells are empty (i.e on fresh sheet) however as the cells referenced already contain Index / Register formula (as listed below).　

=IF(INDEX(Register!$A$3:$AA$988,$A$1+ROW(J1)-1,COLUMN((J1)))="","",INDEX(Register!$A$3:$AA$988,$A$1+ROW(J1)-1,COLUMN((J1))))　

I am guessing there is some conflict between the 2 but I cannot figure this out can anyone help clarify this?　

Thanks in advance



Craig

 

[xlstime]

Can you please upload a sample sheet with required output.

brief idea is to use

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) and put condition formatting using like

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =1 then red color condition


=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =2 then black color condition

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =3 then green color condition

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =4 then anyother color condition

I am in the process of using the Index & Register commands to build a summary sheet fro a much larger database.　

I have got this portion sorted no problems, however what I am trying to do is to use conditional formatting in conjunction with these cells to　

highlight time constrained tasks / deadlines and upcoming deadlines.　

The formulas I am trying to use are as follows:　

=OR(AND(J36="",TODAY()-H27>15),AND(J36<>"",J36-H27))　

Where:　

H27 = Start date 　

J36 = Completion date (if entered)　

So what I am trying to do is to highlight J36 Red if the cell is empty and todays date is >15 days after the date in H27 or J36 has a date entered which is > 15 days after H27.　

I also have 2 further conditions which should highlight the cells yellow or green (both variations on the formula above)　

This works OK if the cells are empty (i.e on fresh sheet) however as the cells referenced already contain Index / Register formula (as listed below).　

=IF(INDEX(Register!$A$3:$AA$988,$A$1+ROW(J1)-1,COLUMN((J1)))="","",INDEX(Register!$A$3:$AA$988,$A$1+ROW(J1)-1,COLUMN((J1))))　

I am guessing there is some conflict between the 2 but I cannot figure this out can anyone help clarify this?　

Thanks in advance



Craig

 

[NARAYANK991]

Hi ,

I have tried with both of the following CF rules , and I do not face any problem.

=OR(AND(J36 = "", TODAY() > H27 + 15), J36 > H27 +15)

=IF(J36 = "", TODAY(), J36) > (H27 + 15)

Narayan

 

[craigs23]

Can you please upload a sample sheet with required output.

brief idea is to use

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) and put condition formatting using like

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =1 then red color condition


=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =2 then black color condition

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =3 then green color condition

=If(first_condition,1,snd_condition,2,thrd_condition,3,4) =4 then anyother color condition

Hi Thanks for replying I have uploaded the sample file.xls to dropbox which can be accessed using following link:
https://www.dropbox.com/s/dlrihq74lnvex4s/Sample Sheet.xlsx?dl=0