On sheet2, in cell H1, type:


=IF(ISNUMBER(SMALL(IF(Sheet1!$D$1:$D$50="","",ROW( Sheet1!$D$1:$D$50)),ROW(1:1))),
INDIRECT("Sheet1!D"&SMALL(IF(Sheet1!$D$1:$D$50="", "",ROW(Sheet1!$D$1:$D$50)),ROW(1:1))),"")

then press Ctrl+Shift+Enter to enter the array formula

Copy down to cell H50.

It's based on the formula by Peo Sjoblom, shown he

http://www.contextures.com/xlDataVal03.html

Brian wrote:
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.





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html