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.

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.

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.

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.

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

Hi,

I am sure there are better solutions to this problem but the one i am
proposoing is a non Array formula:

Link the list in column D to a worksheet starting from A1 i.e. go to a blank
worksheet and say =D1 and copy this down.

Now somewhere don below in Column C, type the foll formula -
=IF(A1="","",ROW(A1)).

In Cell D1, type the foll. formula

IF(ISERROR(INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13: $C$21,ROW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1)) ,"",INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13:$C$21,R OW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1))

Hope this helps,

Ashish Mathur





"Brian" wrote:

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.

....thanks very much to everyone that responded...all suggestions have been
helpful.

"Ashish Mathur" wrote:

Hi,

I am sure there are better solutions to this problem but the one i am
proposoing is a non Array formula:

Link the list in column D to a worksheet starting from A1 i.e. go to a blank
worksheet and say =D1 and copy this down.

Now somewhere don below in Column C, type the foll formula -
=IF(A1="","",ROW(A1)).

In Cell D1, type the foll. formula

IF(ISERROR(INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13: $C$21,ROW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1)) ,"",INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13:$C$21,R OW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1))

Hope this helps,

Ashish Mathur





"Brian" wrote:

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.

Ashish,

I have been working with your suggestion but I am getting an error message
in the formula. I have 2 questions:
1) the long formula that is to be in cell D1, is this to be in worksheet 2?
2) I have entered the long formula exactly as you have typed it. Could you
do a quick check to see if there may be a typing error. I am a novice and
would not recognize an error. Also at the end of the first row(of your
formula), after the 3 closed brackets, the next row starts with an open
bracket with no comma etc between it... is this correct? Thanks again.

"Ashish Mathur" wrote:

Hi,

I am sure there are better solutions to this problem but the one i am
proposoing is a non Array formula:

Link the list in column D to a worksheet starting from A1 i.e. go to a blank
worksheet and say =D1 and copy this down.

Now somewhere don below in Column C, type the foll formula -
=IF(A1="","",ROW(A1)).

In Cell D1, type the foll. formula

IF(ISERROR(INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13: $C$21,ROW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1)) ,"",INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13:$C$21,R OW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1))

Hope this helps,

Ashish Mathur





"Brian" wrote:

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.

Ashish,
Please ignore last message... printed page cut off balance of row. I will
try again.

"Ashish Mathur" wrote:

Hi,

I am sure there are better solutions to this problem but the one i am
proposoing is a non Array formula:

Link the list in column D to a worksheet starting from A1 i.e. go to a blank
worksheet and say =D1 and copy this down.

Now somewhere don below in Column C, type the foll formula -
=IF(A1="","",ROW(A1)).

In Cell D1, type the foll. formula

IF(ISERROR(INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13: $C$21,ROW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1)) ,"",INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13:$C$21,R OW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1))

Hope this helps,

Ashish Mathur





"Brian" wrote:

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.

Ashish,

I am still trying...
Just to recap, in worksheet 2, cell a1 =worksheet 1 ,D1 and I have copied
down to D50. In worksheet 2, I have entered your suggested short formula in
C25. In cell d1 of worksheet 2, I entered your long formula.
This does not seem to be working. Do you see any obvious problems with what
I have done? Thank you.


"Ashish Mathur" wrote:

Hi,

I am sure there are better solutions to this problem but the one i am
proposoing is a non Array formula:

Link the list in column D to a worksheet starting from A1 i.e. go to a blank
worksheet and say =D1 and copy this down.

Now somewhere don below in Column C, type the foll formula -
=IF(A1="","",ROW(A1)).

In Cell D1, type the foll. formula

IF(ISERROR(INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13: $C$21,ROW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1)) ,"",INDEX($A$1:$A$9,IF(ISERROR(SMALL($C$13:$C$21,R OW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1))

Hope this helps,

Ashish Mathur





"Brian" wrote:

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.