Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
KR
 
Posts: n/a
Default Condensing a list/range with blank cells to a new list/range without blanks

I have a list of cells, which are all dynamically linked to other stuff in
my workbook. The main purpose is that the non-blank cells are "flags" that
show that some task has not been completed. However, I have a long enough
list that it isn't easy to look at, or print, because each cell is dedicated
to one task.

What I'd like to do is set a new range of cells to show only the "non-blank"
cells from the larger range.

I did put all my source cells in one column.

Can anyone give me an easy formula that will check for each subsequent
non-blank cell? I've been playing around with VLookup, but haven't gotten it
working yet. It is also possible that the first (or first several) cells in
my larger range might be blank, so that makes it a bit harder.

Any help or code snippets would be greatly appreciated!

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.


  #2   Report Post  
Duke Carey
 
Posts: n/a
Default

Have you considered using Autofilter and filtering for Blanks?


"KR" wrote:

I have a list of cells, which are all dynamically linked to other stuff in
my workbook. The main purpose is that the non-blank cells are "flags" that
show that some task has not been completed. However, I have a long enough
list that it isn't easy to look at, or print, because each cell is dedicated
to one task.

What I'd like to do is set a new range of cells to show only the "non-blank"
cells from the larger range.

I did put all my source cells in one column.

Can anyone give me an easy formula that will check for each subsequent
non-blank cell? I've been playing around with VLookup, but haven't gotten it
working yet. It is also possible that the first (or first several) cells in
my larger range might be blank, so that makes it a bit harder.

Any help or code snippets would be greatly appreciated!

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.



  #3   Report Post  
bj
 
Posts: n/a
Default

I like using <data<filters<Autofilter for this type of operation.
Select the column with your flags
use autofilter
select "non-blanks"
this will get all of your non blank data together.
note:this is not a dynamic action. You have ot reselect the non blanks when
things have changed to get an updated list.


"KR" wrote:

I have a list of cells, which are all dynamically linked to other stuff in
my workbook. The main purpose is that the non-blank cells are "flags" that
show that some task has not been completed. However, I have a long enough
list that it isn't easy to look at, or print, because each cell is dedicated
to one task.

What I'd like to do is set a new range of cells to show only the "non-blank"
cells from the larger range.

I did put all my source cells in one column.

Can anyone give me an easy formula that will check for each subsequent
non-blank cell? I've been playing around with VLookup, but haven't gotten it
working yet. It is also possible that the first (or first several) cells in
my larger range might be blank, so that makes it a bit harder.

Any help or code snippets would be greatly appreciated!

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.



  #4   Report Post  
N Harkawat
 
Posts: n/a
Default

Auto filter i guess is the best route but if need a formula here is one:-
Assuming your list is on cell A1:a1000 On cell B1 type this formula below
and copy all the waydown to b1000
=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$10 00<"",ROW($A$1:$A$1000)),ROW(1:1)))),"",INDEX($A$ 1:$A$1000,SMALL(IF($A$1:$A$1000<"",ROW($A$1:$A$10 00)),ROW(1:1))))
array entered(ctrl+shift+enter)





"KR" wrote in message
...
I have a list of cells, which are all dynamically linked to other stuff in
my workbook. The main purpose is that the non-blank cells are "flags" that
show that some task has not been completed. However, I have a long enough
list that it isn't easy to look at, or print, because each cell is
dedicated
to one task.

What I'd like to do is set a new range of cells to show only the
"non-blank"
cells from the larger range.

I did put all my source cells in one column.

Can anyone give me an easy formula that will check for each subsequent
non-blank cell? I've been playing around with VLookup, but haven't gotten
it
working yet. It is also possible that the first (or first several) cells
in
my larger range might be blank, so that makes it a bit harder.

Any help or code snippets would be greatly appreciated!

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.




  #5   Report Post  
KR
 
Posts: n/a
Default

Beautiful, this is exactly what I needed- Thanks!!!!!

"N Harkawat" <nharkawat@hotmail_dot_com wrote in message
...
Auto filter i guess is the best route but if need a formula here is one:-
Assuming your list is on cell A1:a1000 On cell B1 type this formula below
and copy all the waydown to b1000

=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$10 00<"",ROW($A$1:$A$1000)),
ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($A$1:$A $1000<"",ROW($A$1:$A$1000
)),ROW(1:1))))
array entered(ctrl+shift+enter)





"KR" wrote in message
...
I have a list of cells, which are all dynamically linked to other stuff

in
my workbook. The main purpose is that the non-blank cells are "flags"

that
show that some task has not been completed. However, I have a long

enough
list that it isn't easy to look at, or print, because each cell is
dedicated
to one task.

What I'd like to do is set a new range of cells to show only the
"non-blank"
cells from the larger range.

I did put all my source cells in one column.

Can anyone give me an easy formula that will check for each subsequent
non-blank cell? I've been playing around with VLookup, but haven't

gotten
it
working yet. It is also possible that the first (or first several) cells
in
my larger range might be blank, so that makes it a bit harder.

Any help or code snippets would be greatly appreciated!

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are

my
own.






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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Skip blank cells in diagrams hlp Charts and Charting in Excel 9 February 24th 06 03:32 PM
Copy down - special to fill only the blank cells Mike Excel Discussion (Misc queries) 3 April 18th 05 10:08 PM
blank cells R.VENKATARAMAN Excel Discussion (Misc queries) 1 April 6th 05 10:25 AM
What function can make cells shift up when they are blank? Julie Excel Worksheet Functions 0 March 26th 05 08:31 PM
how do you ignore blank cells Kerry Excel Discussion (Misc queries) 1 February 16th 05 02:55 PM


All times are GMT +1. The time now is 04:40 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"