Autofill cell with a value based on another cell's value while still allowed to select from dropdown

Taylor Scott

I have a Sheet with 3 columns that have a restricted dropdown list: 'Not Started' 'In Progress' 'Rework' and 'Complete'.

Column 4 has a restricted dropdown list: 'Submitted' 'Resubmitted' and 'Rework'

A 5th column has a restricted dropdown list: 'Approved' 'Pending' and 'Revise & Resubmit'.

I want the first 4 columns mentioned to be allowed to select from their dropdown list, but if 'Revise & Resubmit' is selected in column 5, column 4 automatically changes to 'Rework'.

Is this even possible to do?

Bassam Khalil

Hi @Taylor Scott

Hope you are fine, you can't make column 4 work as a column format formula & dropdown(single select) in the same time, but you can workaround:

as i see from your sheet column 4 will be "Submitted" if the 3 columns are "Complete" and column 5 is "Approved", and it will be "Resubmitted" if the 3 columns are "Complete" and column 5 is "Pending", and it will be "Rework" if column 5 is "Revise & Resubmit", and it will be Blank if the 3 columns are "not started".

if my assumptions are correct then we can make column 4 value defined by the following column format formula automatically.

Project Management Review =IF([Architect Approval]@row = "Revise & Resubmit", "Rework", IF(AND([Prepare Submittal Data]@row = "Not Started", [Prepare Shop Drawings (E501)]@row = "Not Started", [Engineering Review]@row = "Not Started", [Architect Approval]@row = ""), "", IF(AND([Prepare Submittal Data]@row = "Complete", [Prepare Shop Drawings (E501)]@row = "Complete", [Engineering Review]@row = "Complete", [Architect Approval]@row = "Pending"), "Resubmitted", IF(AND([Prepare Submittal Data]@row = "Complete", [Prepare Shop Drawings (E501)]@row = "Complete", [Engineering Review]@row = "Complete", [Architect Approval]@row = "Approved"), "Submitted"))))

the result will be as the following

if you have more option please inform me to add it in this formula to cover all options.

Taylor Scott

Thank you, Bassam. I appreciate your feedback.

Unfortunately, your assumptions aren't quite correct. I should have provided more info. Here is a screenshot that shows the possible outcomes.

I'm not sure if this will be possible to have both a dropdown and a formula. I appreciate you taking a try at it.

David Fiorino

You can't do that but I am not sure what function that would serve. If the Architect flags it as Revise and Resubmit, the PM would not be able to change their cell if it is forced to say Rework. The PM would be stuck in rework status until the Architect changed their status from Revise and Resubmit.

If the Architect rejects the submittal, instead of changing their column to say revise, perhaps they delete the status in their column since it is no longer their action to work on and change the PM column to say rework.