Excel 2007 : Auto-populate cells based on value in another cell

Originally Posted by dnice24

If A1 = No, then B1 = "N/A" and is locked, else allow selection from drop down list in B1

Modifying the "constant" in B1 per A1 selection would as you say require VBA.

If on the other hand you're just happy to prevent selection from the DV List then you could modify the source of the List accordingly, eg:

B1 - DV Source:
Allow: List
Source: =IF($A1="No",NA(),MyList)
*modify MyList as appropiate - ie range reference / named range

In this way when A1 is No the List can't be selected - avoids VBA obviously - but does not clear B1 and replace with Error Value.
If you're truly desperate for VBA (to generate in cell value) let us know.