title: Excel table default value and data validation without VBA
body: <p>I have a table in Excel for tracking projects. Whenever I create a new row for the table I'd like it to auto-populate one column that the project has "Not Invoiced". I would ALSO like that this column use Data Validation to only allow either "Not Invoiced" or "Invoiced" as content. </p>
  
  <p>I have been able to make both of these things work, but I cannot seem to make them work together without error! The closest I have gotten:</p>
  
  <ul>
  <li><p>Put a formula in the relevant column. Have tried both the super basic <code>="Not Invoiced"</code> as well as an <code>=IF</code> formula based on the blankness of another column. This correctly carries down each time I make a new row.</p></li>
  <li><p>I then add Data Validation on the column which also works fine at first since my default value from my formula is one of the options, HOWEVER when the project does invoice and I select "Invoiced" I then get an error that I'm violating the above formula. From what I've read selecting something from the drop down should just replace the auto-populated formula, but that doesn't seem to happen, it gives me an error that I've violating the column's formula instead.</p></li>
  </ul>
  
  <p>I've read multiple places that if you correctly order things (create table, add formula for default value, then add data validation) the above method should work, but it will not for me and I continue to get the error every time I change to "Invoiced".</p>