Automatic Status Based on Data in Particular Cells

drewmcg

New Member
Joined
Aug 10, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

1628608275061.png


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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,591
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Maybe
Excel Formula:
=IF(M2="","No yet reviewed",IF(AND(O2="",P2=""),"Incomplete","Complete"))
 

drewmcg

New Member
Joined
Aug 10, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
Maybe
Excel Formula:
=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:

1628610764142.png


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,591
Office Version
  1. 365
Platform
  1. Windows
In that case you need to clearly explain what the various criteria are, otherwise I'm just guessing.
 

drewmcg

New Member
Joined
Aug 10, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,591
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=IF(M2&P2&Q2="","No yet reviewed",IF(OR(M2="",P2="",Q2=""),"Incomplete","Complete"))
 
Solution

drewmcg

New Member
Joined
Aug 10, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Brilliant - it works. Thank you so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,591
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,147,497
Messages
5,741,501
Members
423,662
Latest member
Ajmal Khursand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top