Then a worksheet_change macro can work for you. Let us say the cell is
C1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" Then
Sheets("Sheet2").Range("D1:D1000").NumberFormat = Chr(34) &
Range("C1").Value & Chr(34) & "0"
End If
End Sub

Does this help?

Kostis Vezerides