Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Brian
 
Posts: n/a
Default selecting and listing data

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.
  #2   Report Post  
JulieD
 
Posts: n/a
Default

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.



  #3   Report Post  
Brian
 
Posts: n/a
Default

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.




  #4   Report Post  
JulieD
 
Posts: n/a
Default

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.






  #5   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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



  #6   Report Post  
Ashish Mathur
 
Posts: n/a
Default

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.

  #7   Report Post  
Brian
 
Posts: n/a
Default

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

  #8   Report Post  
Brian
 
Posts: n/a
Default

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.

  #9   Report Post  
Brian
 
Posts: n/a
Default

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.

  #10   Report Post  
Brian
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"