Re: Auto populating drop down lists/cells based on conditions
Right - give this a go. You must not override anything in columns F, G or H - I would suggest you protect these.
Example tab 4 - start with a choice at the top of column E and the formulae do the rest. Anything you wish to override is added in E. The extra benefit is you can see at a glance which rows have had overrides applied.