I am trying to create a template. Data from a separate report will be pasted into columns “A:D”.
Columns “E:J” have both a formula and data validation in the same cell. The data validation is a simple drop-down list with the choices “YES” or “NO”.
The formula is =IF(A2="","","NO"), so if data from the other report is pasted into cell A2 then “E2:J2” are populated with the default choice of “NO”.
The problem I am having is that when a job is complete and the “NO” is changed to “YES” the formula is erased and the template becomes useless.
What are my options? From what I am gathering from the forums is that data validation and formulas don’t play well with each other in the same cell. I’m guessing VBA could be used to populate columns E2:J2 if data is pasted into column A?
Any help would be greatly appreciated.

Capture.JPG
MSISharePointReport.xlsx