Formula/Formatting to populate adjacent cell based on initial cell contents

[JenSmith]

Hi Everyone, 

I am creating a template for a report we are developing and i'm trying to add as many 'tricks' in the background as possible to make it easy for all involved and reduce the amount of queries we will receive back from it. 

(Please see screenshot below for references)

Columns F-H are populated by the end user via set answers in a drop down list however, depending on the answer given in column F, the other 2 columns may not need to be completed. 

Is it possible to add some kind of formula/conditional formatting/data validation that auto populates a cells in columns G&H when the exact phrase 'Completed on DS' is selected in the adjacent  cell in column F?

In case other formatting makes a difference - i already have conditional formatting set on all three columns to highlight certain answers. (e.g. answer 'Not Listed in DS' in column F auto formats to Red Bold text) and my 'lists' are saved on a separate tab in the same workbook. 

I have a medium understanding of formula in excel and can usually string something together through trial and error. I've tried several variations of IF formula for this but can't seem to find anything that works how i want it to. ( last thing i tried was '(=IF(F2="Completed on DS",G2+H2="N/A",0)' ) so i'm not sure if my formula knowledge just doesn't go deep enough or if its impossible to do in this way. 

I hope that makes sense, please feel free to ask further questions if I've not explained it clearly enough. 

Thanks in advance for your time and assistance. 

[#2]

Hello @JenSmith,

Perhaps this might be what you're looking for:

In cell G2...

=IF($F2="Completed on DS","N/A",IF($F2="Not Listed on DS","Statement if true",0)) 

Copy over to H2 and down as necessary. 

[#3]

Hi @PReagan , 

Thank you for your response! 

Would this work whilst also having drop down options for all cells in columns G & H? 

Thanks, 
Jen

[#4 · Accepted solution]

As long as the data validation list includes “N/A” and the “Statement if true” from the second if statement, then yes, this would work whilst having a drop down in the same cell.
However... as soon as an option is chosen from the data validation list the formula would be removed from the cell.
I would recommend keeping the drop down and formula in separate cells.

[#5]

@PReagan  Great, thanks so much for the help! :) 

[#6]

@JenSmith 

My pleasure!