Have one cell showing progress status

[censura]

Hi all hope you are well .

My problem is this -

I have created a excel sheet to track loan application progress. What i was after was to have one cell (Status) that would show the current status for each applicant. Applicants enter the progress sheet and are assigned the status 'Prospect' in column L of the sheet.

I have set up 2 conditions in columns AA & AB

In AA i am testing if we have a YES in column Z (we have received the required documents back if not this shows as NO) the formula for this being.
=IF((Z2="YES"), "Loan Client",L2) so if Z2 is YES we have the documents then update to Loan Client if NO then shows status in L2 'Prospect'

The other condition is testing if we have had the documents back within 14 days if not give it the status unresponsive
=IF(AND((A2<TODAY()-14),AA2="Prospect"),"Unresponsive","Prospect")

It is all a bit clumsy what i would like is either in column L to update with

Prospect - Loan Client or if not within 14 days Prospect - Unresponsive

If not possible in L then in another column so this shows on enter the system Status Prospect - either Loan Client or Unresponsive


There are additional steps i need to test on other conditions from other columns but if you can help great.

Thank you

 

[rollis13]

Added cross-post LINK

 

[PeteWright]

Do you have some sample data?
What make it much easier to see what's going on.

 

[Toadstool]

Hi Censura,

I'm finding it difficult to follow but is this what you need?

Censura.xlsx
	A	B									K	L	M												Y	Z
1	Latest Document Date											Status														Documents Returned
2	29-Jan-21											Client														Yes
3	28-Jan-21											Prospect														No
4	27-Jan-21											Client														Yes
5	26-Jan-21											Prospect														No
6	25-Jan-21											Client														Yes
7	24-Jan-21											Client														Yes
8	24-Jan-21											Prospect														No
9	23-Jan-21											Unresponsive														Yes
10	22-Jan-21											Prospect														No
11	21-Jan-21											Unresponsive														Yes
Sheet1

Cell Formulas
Range	Formula
L2:L11	L2	=IF(AND(Z2="Yes",A2+14>=TODAY()),"Client",IF(Z2="Yes","Unresponsive","Prospect"))

 

[Fluff]

@censura

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Link supplied in post#2
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.