software

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..

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

quot;Dannycolquot; wrote:

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

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

Dannycol

quot;JMBquot; wrote:

gt; I would use the AutoFilter (Data/Filter/AutoFilter) on column CM. Select
gt; column CM, turn on AutoFilter, use non-blank for criteria. Then copy/paste
gt; to the other worksheet
gt;
gt; quot;Dannycolquot; wrote:
gt;
gt; gt; I have an excel file with 4 work sheets.. in sheet named 'Claim sheet' i
gt; gt; have data (numbers and text) in cells CM10:CM1661 which is entered
gt; gt; periodically and deleted via formula linked with other cells in other sheets,
gt; gt; the majority of the 1661 rows are normally blank. What i need to do is on
gt; gt; sheet named 'Statement' list (starting in cell B10) all data in 'Claim sheet'
gt; gt; CM10:CM1661 without the blank rows. The number of rows with data should never
gt; gt; be more than 16 at any one time, so on sheet 'Statement' the data can be
gt; gt; listed in cells B10:B26.
gt; gt;
gt; gt; Any help with the right formula will be much appreciated..

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$1661lt;gt;quot;quot;,ROW(INDIRECT(quot;1:quot;amp;ROWS(Sheet1!$CM$ 10:$CM$1661))),quot;quot;),ROW()))

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

gt; Thanks for advice but this method is not good for me as the data in both
gt; sheets are for viewing or printing only..
gt;
gt; Dannycol
gt;
gt; quot;JMBquot; wrote:
gt;
gt; gt; I would use the AutoFilter (Data/Filter/AutoFilter) on column CM. Select
gt; gt; column CM, turn on AutoFilter, use non-blank for criteria. Then copy/paste
gt; gt; to the other worksheet
gt; gt;
gt; gt; quot;Dannycolquot; wrote:
gt; gt;
gt; gt; gt; I have an excel file with 4 work sheets.. in sheet named 'Claim sheet' i
gt; gt; gt; have data (numbers and text) in cells CM10:CM1661 which is entered
gt; gt; gt; periodically and deleted via formula linked with other cells in other sheets,
gt; gt; gt; the majority of the 1661 rows are normally blank. What i need to do is on
gt; gt; gt; sheet named 'Statement' list (starting in cell B10) all data in 'Claim sheet'
gt; gt; gt; CM10:CM1661 without the blank rows. The number of rows with data should never
gt; gt; gt; be more than 16 at any one time, so on sheet 'Statement' the data can be
gt; gt; gt; listed in cells B10:B26.
gt; gt; gt;
gt; gt; gt; Any help with the right formula will be much appreciated..

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..

RegardsJMBquot; wrote:

gt; The sheet is protected so that you cannot use a filter?
gt; In sheet2 enter this somewhere in the first row and hit Control Shift Enter
gt; and copy down until you get an error.
gt;
gt; =INDEX(Sheet1!$CM$10:$CM$1661,SMALL(IF(Sheet1!$CM$ 10:$CM$1661lt;gt;quot;quot;,ROW(INDIRECT(quot;1:quot;amp;ROWS(Sheet1!$CM$ 10:$CM$1661))),quot;quot;),ROW()))
gt;
gt; If you cannot enter it in the first row then change
gt; ROW()
gt; to
gt; ROW() - (Your starting row number - 1)
gt;
gt;
gt; quot;Dannycolquot; wrote:
gt;
gt; gt; Thanks for advice but this method is not good for me as the data in both
gt; gt; sheets are for viewing or printing only..
gt; gt;
gt; gt; Dannycol
gt; gt;
gt; gt; quot;JMBquot; wrote:
gt; gt;
gt; gt; gt; I would use the AutoFilter (Data/Filter/AutoFilter) on column CM. Select
gt; gt; gt; column CM, turn on AutoFilter, use non-blank for criteria. Then copy/paste
gt; gt; gt; to the other worksheet
gt; gt; gt;
gt; gt; gt; quot;Dannycolquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have an excel file with 4 work sheets.. in sheet named 'Claim sheet' i
gt; gt; gt; gt; have data (numbers and text) in cells CM10:CM1661 which is entered
gt; gt; gt; gt; periodically and deleted via formula linked with other cells in other sheets,
gt; gt; gt; gt; the majority of the 1661 rows are normally blank. What i need to do is on
gt; gt; gt; gt; sheet named 'Statement' list (starting in cell B10) all data in 'Claim sheet'
gt; gt; gt; gt; CM10:CM1661 without the blank rows. The number of rows with data should never
gt; gt; gt; gt; be more than 16 at any one time, so on sheet 'Statement' the data can be
gt; gt; gt; gt; listed in cells B10:B26.
gt; gt; gt; gt;
gt; gt; gt; gt; Any help with the right formula will be much appreciated..

First of all, ROW(10) is not valid syntax. For what you had in mind the
syntax would be either one of the following (with $$ as necessary):

ROW(10:10)
ROW(A10)
ROWS(1:10)
ROWS(A1:A10)

However, what you really need for your task is to replace the ROW()
expression with

ROW()-ROW($A$10) 1 or
ROW()-ROW($A$9)

For your error handling you have a Conditional Format solution and a
modification of JMB's formula solution.

With CF you can use FormulaIs and formula for G10
=ISERROR(G10)
and set font color to white for this condition.

The more complex formula is to trap the error that SMALL might create
if ROW()-ROW($A$9) becomes more than the non-blank cells. Thus you need
the longer formula:

=IF(ISERROR(SMALL(IF(Sheet1!$CM$10:$CM$1661lt;gt;quot;quot;,RO W(INDIRECT(quot;1:quot;amp;ROWS(Sheet1!$CM$10:$CM$1661))),quot;quot;) ,ROW()-ROW($A$9))),quot;quot;,INDEX(Sheet1!$CM$10:$CM$1661,SMALL( IF(Sheet1!$CM$10:$CM$1661lt;gt;quot;quot;,ROW(INDIRECT(quot;1:quot;amp;RO WS(Sheet1!$CM$10:$CM$1661))),quot;quot;),ROW()-ROW($A$9))))

Does this help?

Kostis VezeridesVezerid..

Many thanks. i dont fully understand the reasoning, but thanks again, the
long formula works great, i've also done the same with a few more columns and
changed the cell references, works great...

Regards

quot;vezeridquot; wrote:

gt; First of all, ROW(10) is not valid syntax. For what you had in mind the
gt; syntax would be either one of the following (with $$ as necessary):
gt;
gt; ROW(10:10)
gt; ROW(A10)
gt; ROWS(1:10)
gt; ROWS(A1:A10)
gt;
gt; However, what you really need for your task is to replace the ROW()
gt; expression with
gt;
gt; ROW()-ROW($A$10) 1 or
gt; ROW()-ROW($A$9)
gt;
gt; For your error handling you have a Conditional Format solution and a
gt; modification of JMB's formula solution.
gt;
gt; With CF you can use FormulaIs and formula for G10
gt; =ISERROR(G10)
gt; and set font color to white for this condition.
gt;
gt; The more complex formula is to trap the error that SMALL might create
gt; if ROW()-ROW($A$9) becomes more than the non-blank cells. Thus you need
gt; the longer formula:
gt;
gt; =IF(ISERROR(SMALL(IF(Sheet1!$CM$10:$CM$1661lt;gt;quot;quot;,RO W(INDIRECT(quot;1:quot;amp;ROWS(Sheet1!$CM$10:$CM$1661))),quot;quot;) ,ROW()-ROW($A$9))),quot;quot;,INDEX(Sheet1!$CM$10:$CM$1661,SMALL( IF(Sheet1!$CM$10:$CM$1661lt;gt;quot;quot;,ROW(INDIRECT(quot;1:quot;amp;RO WS(Sheet1!$CM$10:$CM$1661))),quot;quot;),ROW()-ROW($A$9))))
gt;
gt; Does this help?
gt;
gt; Kostis Vezerides
gt;
gt;

Danny,

the formula JMB suggested had the following skeleton:

=INDEX(data,SMALL(IF(conditions),ROW())

The IF(Conditions) is generating a virtual array, which only only
includes non-blank rows. So, SMALL(..., ROW()) was to find first the
smallest, then the second smallest etc, of this array. If the virtual
array has fewer elements than the current row, ROW() will try to find
an element that does not exist. Also, if your data start in row 10, the
formula would start to find the 10th element, then 11th etc.

So, first modification was to subtract 9 from ROW(). We used ROW($A$9)
in order to show exactly which cell is used to start the data. This
leads us to the formula:

=INDEX(data,SMALL(IF(conditions),ROW()-ROW($A$9))

The second modification, which led to this length, was to see which
part could create the error, and this would be SMALL. This leads us to
the general philosophy of trapping errors:

=IF(ISERROR(formula),quot;quot;,formula).

This can be simplified if instead of using the entire formula in
ISERROR, we use only the offending part. So your formula became:

=IF(ISERROR(LARGE(...),quot;quot;,modifed_formula)

which is what you used.

HTH
KostisMany thanks for the info...

Regards

quot;vezeridquot; wrote:

gt; Danny,
gt;
gt; the formula JMB suggested had the following skeleton:
gt;
gt; =INDEX(data,SMALL(IF(conditions),ROW())
gt;
gt; The IF(Conditions) is generating a virtual array, which only only
gt; includes non-blank rows. So, SMALL(..., ROW()) was to find first the
gt; smallest, then the second smallest etc, of this array. If the virtual
gt; array has fewer elements than the current row, ROW() will try to find
gt; an element that does not exist. Also, if your data start in row 10, the
gt; formula would start to find the 10th element, then 11th etc.
gt;
gt; So, first modification was to subtract 9 from ROW(). We used ROW($A$9)
gt; in order to show exactly which cell is used to start the data. This
gt; leads us to the formula:
gt;
gt; =INDEX(data,SMALL(IF(conditions),ROW()-ROW($A$9))
gt;
gt; The second modification, which led to this length, was to see which
gt; part could create the error, and this would be SMALL. This leads us to
gt; the general philosophy of trapping errors:
gt;
gt; =IF(ISERROR(formula),quot;quot;,formula).
gt;
gt; This can be simplified if instead of using the entire formula in
gt; ISERROR, we use only the offending part. So your formula became:
gt;
gt; =IF(ISERROR(LARGE(...),quot;quot;,modifed_formula)
gt;
gt; which is what you used.
gt;
gt; HTH
gt; Kostis
gt;
gt;