View Single Post
  #10   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


w1 (source)

Insert 1 row before the data such that A2:D21 houses the data.

In E1 enter: 0
In E2 enter & copy down:

=IF(C2<"",LOOKUP(9.99999999999999E+307,$E$1:E1)+1 ,"")

w2 (destination)

In A1 enter:

=LOOKUP(9.9999999999999E+307,Sheet1!E2:E21)

In A2 enter & copy down:

=IF(ROW()-ROW(A$2)+1<=$A$1,INDEX(Sheet1!$C$2:$C$21,MATCH(ROW ()-ROW(A$2)+1,Sheet1!$E$2:$E$21)),"")

Shooter Wrote:
My worksheet (w1) has values in cells A1:D20. All cells have a formula
or
link to another worksheet. Some of the cells are blank (but still have
a
formula in the cell). All of the cells change periodically as I change
the
values in other worksheets. In worksheet 2 (w2) A1:a20, I would like
to list
all of the values of w1 C1:c20 but without any blank rows. I would like
a
formula that will do this automatically. Thanks for your suggestions.
--
Shooter



--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=276804