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

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?


Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/28/21

    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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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.

  • 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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!