Prioritise conditions needed...

[Davin1981]

Hi

I need to set several function in a single formula but no matter what I do, excel will prompt errors...

Could someone please help me with the following:


Admin date Deadline Amount A Amount B Status


The Admin date will source for data from another table via VLookup.
The Deadline is auto computed using excel formula to calculate using Admin date
Amount A and Amount B are to be manually entered

I am stuck with the formula for the Status...

Priority 1: To check if there is feedback(from VLookup) of information for Admin date, if none, to set Status as 'N/A'
Priority 2: If there is feedback for Admin date, to check whether the Deadline has past today's date. If yes, to set Status as 'Overdue'
Priority 3: If Deadline has not past today's date, to check whether there is any value in both Amount A or Amount B. If both are blank, to set Status as 'Pending' BUT if there is any amount in either Amount A or Amount B, to set the Status as 'Complete'.

Hope someone can help me with the above situation urgently please...

Thank you...

 

[Green Crocodile]

Re: Priortise conditions needed...

Can you please provide a sample workbook to work with?

 

[Peter_SSs]

Re: Priortise conditions needed...

Welcome to the MrExcel board!

The following is a little vague.

.. if there is feedback(from VLookup) of information for Admin date, if none, ..

I will assume that column A will have from the VLOOKUP either a date or a null string "". If that is not the case please explain again and/or give a few examples of what might be in column A and what you mean by "no feedback".

In cell E2, try

=IF(A2="","N/A",IF(B2<TODAY(),"Overdue",IF(C2&D2="","Pending","Complete")))

 

[Davin1981]

Re: Priortise conditions needed...

Admin date Deadline Amount A Amount B Status
vlookup Source (Nil data) N/A
1/1/2016 30/4/2016 Overdue
1/1/2016 30/4/2016 100 200 Complete
1/8/2016 31/12/2016 Pending
1/8/2016 31/12/2016 100 200 Complete

 

[Davin1981]

Re: Priortise conditions needed...

Sorry... I do not know how to add excel table here...

 

[Peter_SSs]

Re: Priortise conditions needed...

Try

Excel Workbook
	A	B	C	D	E
1	Admin date	Deadline	Amount A	Amount B	Status
2					N/A
3	1/01/2016	30/04/2016			Overdue
4	1/01/2016	30/04/2016	100	200	Complete
5	1/08/2016	31/12/2016			Pending
6	1/08/2016	31/12/2016	100	200	Complete
Status

Sorry... I do not know how to add excel table here...

See my signature block below.

 

[Davin1981]

Re: Priortise conditions needed...

Welcome to the MrExcel board!

The following is a little vague.I will assume that column A will have from the VLOOKUP either a date or a null string "". If that is not the case please explain again and/or give a few examples of what might be in column A and what you mean by "no feedback".

I mean Column A has a formula to vlookup to another table 'B' for data. If there is no data from table 'B', Column A will not receive any date.

 

[Davin1981]

Re: Priortise conditions needed...

It works!!! Thank you very much Peter!!!

 

[Davin1981]

now I will have to deal with my vlookup result... it gives me invalid date... 00/01/1990...

 

[Peter_SSs]

now I will have to deal with my vlookup result... it gives me invalid date... 00/01/1990...

Maybe
=IF(your_vlookup_formula = 0,"",your_vlookup_formula)


If that doesn't do it, please post your current vlookup formula.