Feb 13 2020 05:28 AM
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.
Feb 13 2020 06:24 AM
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.
Feb 13 2020 06:41 AM
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
Feb 13 2020 10:44 AM - edited Feb 13 2020 10:45 AM
SolutionFeb 14 2020 03:08 AM
@PReagan Great, thanks so much for the help! :)
Feb 14 2020 06:33 AM
Feb 13 2020 10:44 AM - edited Feb 13 2020 10:45 AM
Solution