Hi Brian

you can't do this without using code ... here's some that will do what you
want, but be warned when you change the values in column D of worksheet 1
the values in the whole of column A on sheet2 are deleted and replaced with
the new values.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Column = 4 Then
Sheets("Sheet2").Range("A:A").Delete
Columns("D:D").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Copy Sheets("Sheet2").Range("A1")
Application.CutCopyMode = False
Range("D1").Select
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

This code needs to go against the Sheet1 module (right mouse click on
Sheet1's tab, choose view code - Sheet1 should be highlighted on the left
hand side of the screen - paste the code in the white sheet of paper on the
right). Oh, you will also need to ensure that your security is set to
medium (tools / macros / security)

Hope this helps
Cheers
JulieD
"Brian" wrote in message
...
Thanks, however I was hoping to have this excercise automated in some way.
Each time I change the data in worksheet 1 column D, I would like the
values
in worksheet 2 column A to automatically update as described(without blank
cells). Would anyone have any suggestions to do this? Thanks again for any
further input.

"JulieD" wrote:

Hi Brian

click on column D choose edit / goto - then click the SPECIAL BUTTON
choose CONSTANTS then click OK
now copy & paste the information as normal and the blank spaces will not
be
brought over.

Cheers
JulieD

"Brian" wrote in message
...
My worksheet (worksheet1)has data in cells a1:H50. Column D has some
blank
cells and data in other cells. In worksheet 2, I would like to list in
column
A, all of the values in worksheet1- (D1:D50) but with no blank cells in
the
list. Thanks for help with this formula.