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