Auto-changing status: Multiple IFs and AND function question.

[tickles]

LATEST UPDATE HERE.


UPDATE:

Hi guys, thanks so much for the starting tips.

Unfortunately, I've taken both your advice and codes and somehow I still cannot get it to work.

Been at it for about 2 hours and I'm getting increasingly frustrated (and Googling didn't really provide more help than you guys already gave me). Haha.

Perhaps I'll provide an even clearer picture of what I want to achieve (Picture Below):

Excel File:


========== first post ===========

Hi guys,

I'm a total excel noob. Have something I'm trying to achieve illustrated below:

qryTw6s.png

Attachment 326483

The cells are intended to be either blank, or will be populated with dates. Status will change according to the different combinations of whether either cell is filled with content (dates), or both cells are filled, or neither cells are filled; giving 4 statuses: Nothing/AllFilled/AFilled/XFilled.

I tried a combination of IF, AND, ISTEXT, ISBLANK functions, but have been having trouble with getting the order of the logic written to work. And i also have trouble with the commas and parentheses.

Something i tried and didn't work: =IF(AND(ISBLANK(A2),ISBLANK(B2)),"In-Progress",(if(and(istext(A2),isblank(b2),"Process Complete"))"")...

It's kinda half done, but it just can't figure out my way round it to get the desired effect.

I'm hoping some geniuses here will enlighten me.

Thanks so much!

[Hawkeye16]

Try this

Please Login or Register  to view this content.

[FDibbins]

Or this, slightly shorter...
=IF(A2="",IF(B2="","Nothing","Xfilled"),IF(B2="","Afilled","Allfilled"))

[tickles]

Hi guys, thanks so much for the starting tips.

Unfortunately, I've taken both your advice and codes and somehow I still cannot get it to work.

Been at it for about 2 hours and I'm getting increasingly frustrated (and Googling didn't really provide more help than you guys already gave me). Haha.

Perhaps I'll provide an even clearer picture of what I want to achieve (Picture Below):

Capture.PNG

[FDibbins]

Please upload a sample of your workbook, not a picture of your data.
Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[tickles]

TestAutoStatus.xlsx

My apologies.

Excel file attached. First post updated as well.

[FDibbins]

Your file is a bit different from your explanation. Apart from the wording (no real biggie) you now have 3 columns?

Also, it seems you have some discrepancies?

	A	B	C	D
1	READY	EXECUTED	ENDED	STATUS
2	1-Jun-14	3-Jun-14	2-Jun-14	Full
3				Waiting
4	1-Jun-14			Ready
5	1-Jun-14	3-Jun-14		Full
6	1-Jun-14		2-Jun-14	Ended
7		3-Jun-14	2-Jun-14	Waiting
8			2-Jun-14	Waiting
9		3-Jun-14		Waiting

Row 3 is empty, D3=waiting
B7:C7 contain dates, D7=waiting

I think you need to define your rules for me again?

[tickles]

Yup, now there are three.

The "Ready" column is crucial. As long as the 'Ready" column is empty, whatever the state of the other columns, the status should always be "Waiting".

However, the status starts changing (from "Waiting") only when (1) Ready column is filled; with various filled/unfilled combinations of Col B and Col C.

Assuming Col A is filled, then if B is filled while C is blank or filled, Status = Full (Rows 2 and 5)

Assuming COl A is filled, then if B is blank, while C is filled, Status = Ended (Row 6)

Assuming Col A is filled, then if B is blank, while C is blank, Status = Ready (Row 4)

Assuming Col A is blank, whether or not B and C is filled/blank, Status = Waiting (Rows 2, 7, 8 and 9)

	A	B	C	D
1	READY	EXECUTED	ENDED	STATUS
2	1-Jun-14	3-Jun-14	2-Jun-14	Full
3				Waiting
4	1-Jun-14			Ready
5	1-Jun-14	3-Jun-14		Full
6	1-Jun-14		2-Jun-14	Ended
7		3-Jun-14	2-Jun-14	Waiting
8			2-Jun-14	Waiting
9		3-Jun-14		Waiting

Does this explain things? Sorry if I've confused everyone by not being elaborate enuff.

[tickles]

Bump for this evening.

[FDibbins]

Thanks for the update and sorry for the delay in replying.

Try this, copied down...
=IF(A2="","Waiting",IF(B2="",IF(C2="","Ready","Ended"),"Full"))