Data validation overwrites formula contained in same cell

[jdhill]

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

[davesexcel]

Hi,
Maybe if A2 does not ="" then you want it to read YES

=IF(A2="","","NO","YES")

Would take care of the problem.

[jdhill]

Thanks for looking into this. My problem is that the worksheet would be blank until data is pasted into columns A:D. I don't want "YES" or "NO" to be showing unless data has been pasted into the corresponding rows/columns. Forgive me for my poor explanation.
So upon opening the workbook the user would only see the column titles in A1:J1. Everything else would be blank. If the user pasted data into A2:D25 then the range of cells from E2:J25 would default to "NO" (with the formula =IF(A2="","","NO")) and the user could then select "YES" from the drop down validation list once appropriate. This would need to happen without the formula being overwritten so the template could be reused.
I'm thinking that this would need to be accomplished through VBA?
Any suggestions?
Thanks again for the help.

[jdhill]

Is there a way to run the formula =IF(A2="","","NO") in VBA?

[davesexcel]

Try your macro recorder.
Select the entire range you want the formula.
Type in the formula and hit Ctrl & Enter be sure to hit F4 when you select A2 to indicate A2 as absolute.

[jdhill]

I ran the macro recorder and got the following code:

Please Login or Register  to view this content.

This correctly inserts the default "NO" but it still overrides the data validation when the choice is changed from "NO" to "YES"

[davesexcel]

Yes, The data Validation will overwrite the formula.
You can either have one or the other.

Your application does not seem very practical as the user should not have to select Yes Or No why can't the formula decide that?

[jdhill]

This is a way of tracking work (I know it's not the most practical way to accomplish this, but it's what the boss wants). By default all of the columns should be "NO" because the job has not been completed, once the appropriate person has completed the job they will need to manually select "YES" which can't be decided by the formula. So, you answered part of my question...I can have one or the other (data validation or formula). Is there any possible way through VBA to accomplish what I am trying to do?

I do appreciate your help, I struggle with this stuff.