selecting and listing data

[Brian]

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- (D150) but with no blank cells in the
list. Thanks for help with this formula.

 

[JulieD]

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]

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]

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").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

 

[Debra Dalgleish]

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 here:

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

 

[Ashish Mathur]

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,ROW(A1))),"",SMALL($C$13:$C$21,ROW(A1))),1))

Hope this helps,

Ashish Mathur

 

[Brian]

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

 

[Brian]

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.

 

[Brian]

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

 

[Brian]

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.