Simple question - auto populate cells - dependent on other cell value.

[David Smithz]

How easy is it to have a cell that has a drop down box in it containing two
values. (e.g. yes / no).

If one value is selected (e.g. yes), the five cells below that cell are auto
populated with a value, if the other value is selected they are blanked so
free text can be added.

I assume this will mean I have to delve into the realm of Macro writing (I
have programmed in VB6 so should be able to get on with syntax / object
model etc) but if I don't then that will be a bonus.

Thanks

David

 

[David Smithz]

Stop the press - I think I've cracked. Found a useful example on Google.
Will update is I find it works.

 

[davesexcel]

you could try a worksheet change event, I just recorded this with the
macro recorder then copied and pasted it into the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A1"), Target).Address = Range("A1").Address Then
Range("A2:A6").Select
Selection.FormulaR1C1 = "=IF(R1C1=""Yes"",100,"""")"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=IF(R1C1=""Yes"",200,"""")"
Range("A4").Select
ActiveCell.FormulaR1C1 = "=IF(R1C1=""Yes"",300,"""")"
Range("A5").Select
ActiveCell.FormulaR1C1 = "=IF(R1C1=""Yes"",400,"""")"
Range("A6").Select
ActiveCell.FormulaR1C1 = "=IF(R1C1=""Yes"",500,"""")"
Range("A7").Select


End If
End Sub