Formula help needed please

[BecciB]

I have the following data:

H I J K L Case Status Allocated To Date Allocated Sate of Assessment Date Case Closed Minnie 17/03/17 26/04/17 Daffy 25/04/17 05/05/17 01/08/17 Pluto 02/05/17 10/05/17 <tbody> </tbody>

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

I would like the following to appear in column H when these parameters are met:
Awaiting Allocation to appear in column H - When there is no data in column K
Allocated to appear in column H - when there is any date recorded in column K
Closed to appear in column H - when there is a date added to column L

I am rubbish at formulas so i's be really grateful for some urgent help.

Thank you in advance

 

[BGY23]

Try This in H2

=IF(L2<>"","Closed",IF(ISBLANK(K2),"Awaiting Allocation",IF(CELL("format",K2)="D1","Allocated")))

<colgroup><col width="72" style="width: 54pt;" span="2"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2528;" span="2"> <col width="72" style="width: 54pt;" span="4"> <tbody> </tbody>

 

[FormR]

Hi, just another option to try:

=IF(L2>0,"Closed",IF(K2>0,"Allocated","Awaiting Allocation"))

 

[BecciB]

This is great, thanks!

How do I get subsequent fields to appear blank until i have entered some parameters? How would i add that to the formula?

 

[BGY23]

The first part tests to ensure that K2 and L2 are both blank and will give a blank result. The rest works as above =IF(AND(ISBLANK(K2),ISBLANK(L2)),"",IF(L2<>"","Closed",IF(ISBLANK(K2),"Awaiting Allocation",IF(CELL("format",K2)="D1","Allocated"))))

<tbody>
</tbody><colgroup><col span="13"></colgroup>

 

[BGY23]

Just tested it and the new element needs to got at the end

=IF(L8<>"","Closed",IF(ISBLANK(K8),"Awaiting Allocation",IF(CELL("format",K8)="D1","Allocated",IF(AND(ISBLANK(K8),ISBLANK(L8)),"",))))

<colgroup><col width="72" style="width: 54pt;" span="2"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2528;" span="3"> <col width="72" style="width: 54pt;" span="8"> <tbody> </tbody>

 

[FormR]

How do I get subsequent fields to appear blank until i have entered some parameters?

Hi, "some" isn't a very solid description, but for my suggestion maybe:

=IF(COUNTA(I2:L2),IF(L2>0,"Closed",IF(K2>0,"Allocated","Awaiting Allocation")),"")

Which returns a result if at least one of the cells in I2:L2 contains a value else it returns blank.