Frank,

The details are as follows:
data in worksheet 10 (w10) aj7:aj207. There is also data in columns a-ap
which may not be relevant. In worksheet 22, B9 I have entered the following
formula (array formula):
=if(iserror(index('sheet10'$aj$7:$aj$207,small(if( 'sheet10'$aj$7:$aj$207<"",row('sheet10'$aj$7:$aj$ 207)),row(1:1)))),"",index('sheet10'$aj$7:$aj$207, small(if('sheet10'$aj$7:$aj$207<"",row('sheet10'$ aj$7:$aj$207)),row(1:1)))).
Then I copied down to B209. Thanks for taking another look at this.



"Frank Kabel" wrote:

Hi
sounds like you haven't change all ranges accordingly :-) Post the
formula you have tried

--
Regards
Frank Kabel
Frankfurt, Germany

"Brian" schrieb im Newsbeitrag
...
Frank,
I am using your formula below and it works well in one of my
workbooks. I
have tried the same formula in another workbook and the cells remain
blank.
Do you have any "trouble shooting" ideas?
In summary, my data is in worksheet 10, cells aj1:aj200. These cells
have
existing simple formulas that are linked to other worksheets. I
entered your
formula in worksheet 22, B9. I entered formula as array formula and
copied
down to B209. No error messages with the formula but B9:B209 remain
blank.
Would you have any suggestions? Thanks.


"Frank Kabel" wrote:

Hi
try:

=IF(ISERROR(INDEX('sheet1'!$C$1:$C$20,SMALL(IF('sh eet1'!$C$1:$C$20<"",

ROW('sheet1'!$C$1:$C$20)),ROW(1:1)))),"",INDEX('sh eet1'!$C$1:$C$20,SMAL
L(IF('sheet1'!$C$1:$C$20<"",ROW('sheet1'!$C$1:$C$ 20))


--
Regards
Frank Kabel
Frankfurt, Germany

"Shooter" schrieb im
Newsbeitrag
...
Frank,

If I put this formula in worksheet 2 , cell B2, how should I
modify
the
formula to do the same thing as we did in cell e1 of worksheet 1?
Thanks.

"Frank Kabel" wrote:

Hi
put the following formula in E1 for example (entered as array
formula
with CTRL+SHIFT+ENTER):



=IF(ISERROR(INDEX($C$1:$C$20,SMALL(IF($C$1:$C$20< "",ROW($C$1:$C$20)),R


OW(1:1)))),"",INDEX($C$1:$C$20,SMALL(IF($C$1:$C$20 <"",ROW($C$1:$C$20))
,ROW(1:1))))

and copy this formula down to D20

--
Regards
Frank Kabel
Frankfurt, Germany

"Shooter" schrieb im
Newsbeitrag
...
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