Thanks again..

I can insert the formula into row 1 and copied it down, this returns the
data ok but the blank cells return #NUM. instead of leaving the cells blank?

I need the data listing in Statement sheet (sheet 4) starting in row 10
(B10) when i change the formula Row() to Row(10) i get the usual error ..
formula contains an error..

Any ideas..

Regards


JMB" wrote:

The sheet is protected so that you cannot use a filter?
In sheet2 enter this somewhere in the first row and hit Control+Shift+Enter
and copy down until you get an error.

=INDEX(Sheet1!$CM$10:$CM$1661,SMALL(IF(Sheet1!$CM$ 10:$CM$1661<"",ROW(INDIRECT("1:"&ROWS(Sheet1!$CM$ 10:$CM$1661))),""),ROW()))

If you cannot enter it in the first row then change
ROW()
to
ROW() - (Your starting row number - 1)


"Dannycol" wrote:

Thanks for advice but this method is not good for me as the data in both
sheets are for viewing or printing only..

Dannycol

"JMB" wrote:

I would use the AutoFilter (Data/Filter/AutoFilter) on column CM. Select
column CM, turn on AutoFilter, use non-blank for criteria. Then copy/paste
to the other worksheet

"Dannycol" wrote:

I have an excel file with 4 work sheets.. in sheet named 'Claim sheet' i
have data (numbers and text) in cells CM10:CM1661 which is entered
periodically and deleted via formula linked with other cells in other sheets,
the majority of the 1661 rows are normally blank. What i need to do is on
sheet named 'Statement' list (starting in cell B10) all data in 'Claim sheet'
CM10:CM1661 without the blank rows. The number of rows with data should never
be more than 16 at any one time, so on sheet 'Statement' the data can be
listed in cells B10:B26.

Any help with the right formula will be much appreciated..