Excel Expert Needed - How can I get Excel to dynamically update one cell based on the input of a num-CodePudding

I wish there is an Excel Expert out there who can solve this for me.

I'm making a little spreadsheet tacker for migrating servers.

I want excel to dynamically change a particular cell value based on input from a number of different cells. For example, lets say cell "C1" is the cell that should change dynamically and cells "D1, E1 and F1" are the cells where I input something. These three input cells all have a 'drop down' list of three options each to select from. The drop down options are "In Progress, Problem, Done".

I want excel to update cell "C1" (the dynamically changing cell) if I select "Done" from the input selection in cell "D1". The end result in "C1" should now display "Done". Likewise, if I select "Problem" in cell "D1" from the selection list, then the end result in "C1" should now display "Problem" instead.

Now it gets a little more complex and unsure if Excel can do this for me, I will explain further. If cell "D1" is selected as "Done" and cell "E1" is selected as "Problem", then I want Excel to update cell "C1" with "Problem" and ignore what was previously displayed in cell "C1", which was "Done". Basically, if any of my input cells are selected with "Problem" then I want my cell "C1" (dynamically changing cell) to always say "Problem", even if any of the other input cells are showing as "Done".

If there is anybody out there who can help by providing a formula that can achieve the above then it will be greatly appreciated.

Thank you in advance.

CodePudding user response：

You can use the following formula in Cell C1: =IF(OR(D1="Problem";D1="Problem";F1="Problem");"Problem";IF(OR(D1="In Progress";E1="In Progress";F1="In Progress");"In Progress";"Done"))

This should solve your problem. Basically, what is being considered here is that if any cell has a problem, then C1 will show Problem, if there's two cells with "Done" and another one with value "In Progress", then this will show as "In Progress". And last but not least, if all cells are "Done", then C1 will be equal "Done".

CodePudding user response：

I believe this should be enough

=IF(OR(D1="Problem",E1="Problem",F1="Problem"),"Problem",D1)

You said that the value in C1 depends on D1, but if there is a Problem in E or F.

CodePudding user response：

Or you can go the shorter route:

=if(and(D1="Done",E1="Done",F1="Done"),"Done","In Progress")

So, if all 3 are Done then Done if not then in progress. You can add another layer to pick up "problem" as you wish.