I have used your formula using 10 columes and 1661 rows and it works fine..
the source data is inserted via formula from other cells, no problem all
works fine, the problem i have is this. when i copy the sheet or save it as a
different name and delete the data so i can start with new data, i find only
2 or 3 colums work the other colums return #Value in the source sheet. Any
help would be appreciated

Regards




adin Akyurek" wrote:


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