Automatic Status Based on Data in Particular Cells

[drewmcg]

Hello,

I'm a first time poster looking for a bit of help. I hope the following not a silly request (but concede it might very well be)!

I am managing a spreadsheet with several hundred rows that colleagues will input data on to - please see a smaller scale example below.

I would like to insert a status column (A) that automatically indicates whether a row is 'complete', 'incomplete', or 'not yet reviewed' (or RAG rated) based on information being entered into a number of key columns (M, P, Q).

I have used the =ISBLANK formula in columns (B, C, D and E) to show whether information has been recorded in the cells but would now like to hide these and have one status column.

I'm also limited because the spreadsheet is based and edited in Microsoft Teams.

Is an automatic status indicator possible?

Thank you in advance for your time.

Best,

Drew

 

[Fluff]

Hi & welcome to MrExcel.
Maybe

=IF(M2="","No yet reviewed",IF(AND(O2="",P2=""),"Incomplete","Complete"))

 

[drewmcg]

Hi & welcome to MrExcel.
Maybe

=IF(M2="","No yet reviewed",IF(AND(O2="",P2=""),"Incomplete","Complete"))

Hi Fluff,

Thanks for taking the time to respond!

Here is how it turns out with the formula you kindly provided:

It's not quite right yet. Based on the dummy data, it should read: 2. Incomplete; 3. Incomplete, 4. Incomplete; 5. Complete; 6. Incomplete.

If it was easier to only have two choices, I would be happy with 'Incomplete' and 'Complete'.

I'm starting to think it might be impossible!

 

[Fluff]

In that case you need to clearly explain what the various criteria are, otherwise I'm just guessing.

 

[drewmcg]

In that case you need to clearly explain what the various criteria are, otherwise I'm just guessing.

Apologies, I've clearly not articulated myself properly.

To consider an entry as complete, a row would have to have data entered into columns M (Criticality), P (FTE) and Q (Impact).

If data is missing from one or more of these columns, it would be considered as incomplete.

Does that make sense?

 

[Fluff]

Ok, how about

=IF(M2&P2&Q2="","No yet reviewed",IF(OR(M2="",P2="",Q2=""),"Incomplete","Complete"))

 

[drewmcg]

Brilliant - it works. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.