Formatting one cell based on the content of multiple other cells

New Contributor

I am trying to format a document to auto fill based on the content of 3 other cells 

 

for example; 

if D9, E9 and F9 (using dropdown list) are all equal to "yes" G9 will auto populate to "yes", But

if any D9, E9, or F9 contain either "in Progress" or "no" G9 will auto populate to Either "In progress" or "no"

 

Jim_Martin_2-1658291142277.png

 

i feel like this should be conditional formatting however cant figure out how to have the cells auto populate how i want them.

 

Ay help would be greatly appreciated.

 

5 Replies

@Jim_Martin Try this:

=IF(COUNTIF(D9:F9,"yes")=3,"yes",IF(COUNTIF(D9:F9,"no")<>0,"no",IF(COUNTIF(D9:F9,"In Progress")<>0,"In Progress", "-")))

Not very elegant, but it works. Note that your screenshot suggest that when both No and In Progress occur, that No should be the end result.

 

@Riny_van_Eekelen Thanks for your help, that works like a charm.

@Riny_van_Eekelen, another quick question for you

On a lesser scale i want to then format another cell to show the contents of cell C9 if G9 is equal to "yes". i tried =IF(G9,"yes")=C9) but that's not right.

@Jim_Martin That would be:

=IF(G9="yes",C9,"")

 

meaning as much as, "If condition is met then value if true, otherwise value if false"