Formula help needed please

[BecciB]

I have the following data:

[TABLE="width: 425"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD]Case Status[/TD]
[TD]Allocated To[/TD]
[TD]Date Allocated[/TD]
[TD]Sate of Assessment[/TD]
[TD]Date Case Closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Minnie[/TD]
[TD]17/03/17[/TD]
[TD]26/04/17
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Daffy[/TD]
[TD]25/04/17
[/TD]
[TD]05/05/17[/TD]
[TD]01/08/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pluto[/TD]
[TD]02/05/17[/TD]
[TD]10/05/17[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

[TABLE="width: 442"]
<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>[TR]
[TD="width: 590, bgcolor: transparent, colspan: 8"]=IF(L2<>"","Closed",IF(ISBLANK(K2),"Awaiting Allocation",IF(CELL("format",K2)="D1","Allocated")))[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="width: 936"]
<tbody>[TR]
[TD="colspan: 13"]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"))))[/TD]
[/TR]
</tbody><colgroup><col span="13"></colgroup>[/TABLE]

 

[BGY23]

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

[TABLE="width: 717"]
<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>[TR]
[TD="width: 957, bgcolor: transparent, colspan: 13"]=IF(L8<>"","Closed",IF(ISBLANK(K8),"Awaiting Allocation",IF(CELL("format",K8)="D1","Allocated",IF(AND(ISBLANK(K8),ISBLANK(L8)),"",))))[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.