Auto-populate cells based on value in another cell

**dnice24** · 11-29-2010, 11:09 PM

I'm creating a configuration tool for instrumentation that has several options. Many of these options are dependent on one another. I have extinguished all my options in non-VBA land, so I need some help. To summarize, I need to:

*Conditionally lock cells (and populate with "N/A") OR allow selection from drop down list - the list exists, it's the locking and auto-population I'm struggling with

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

A simple macro example that does this would really help. I will be able to adjust ranges, values, etc. Thanks in advance for any assistance...

**DonkeyOte** · 11-30-2010, 03:19 AM

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.