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

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

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

Frank, this works great... thank you.

"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

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

Shooter Wrote:
...this works great....
[...]
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



Try to insert 1 or 2 rows before the first formula cell. The suggestion
is quite expensive and non-robust (although corrigible).


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

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

thank you.

"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

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

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

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

Hi
dn't ee an error?. If you like email me this file and I'll have a look
at it

--
Regards
Frank Kabel
Frankfurt, Germany

"Shooter" schrieb im Newsbeitrag
...
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$20
7<"",row('sheet10'$aj$7:$aj$207)),row(1:1)))),"", index('sheet10'$aj$7:
$aj$207,small(if('sheet10'$aj$7:$aj$207<"",row('s heet10'$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

In reply to Aladin post..

I have used the formula you listed but changed the references etc to suit my
sheet, i have 4 sheets were date is linked with several formula, i was
pleased to see how it worked first time, however my problem is that it has
now slowed down the working of my sheet, E.g when i enter date into some of
the cells it takes 2 whole seconds before i can work in another cell.. i can
only put it down to the number of rows and columns i'm using? I have approx
1600 rows of data most of which are empty but do have formula spread over 10
columns. My aim is to list on a seperate sheet all the rows with data without
the blank rows. As i say everything works fine but sadly slow when entering
data. Any suggestions or ways around it?



"Aladin 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

If you want to delete blank rows on your datasheet, here's a routine
which does this:

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

If your formulae are slowing down your worksheet, then maybe try
getting the same result but without formulae. For instance if you
filter your data on your datasheet, then you'll be able to copy the
filtered range onto another sheet. If this meets your needs, you could
look at attaching this filter & paste process to a macro/button.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532460

Thanks for the reply... Im not clued up on codes or macros so not sure what
to do..

This is my situation.. I have 4 work sheets. In one sheet named 'Claim
Sheet' I have possible data in cells CM10:CV1661 all of these cells have
formulae where the data is automatically entered by me entering data
elsewhere on the same sheet, summarily the data is deleted when required. No
problem here..

What Im trying to achieve is this.. On a separate work sheet named
'Statement' I need to list all rows with data only from 'Claim Sheet'
CM10:CV1661 excluding all blank rows. The list of rows needs to appear on
'Statement' B10:K25, 16 rows is sufficient as the number of rows with data
from 'Claim Sheet' will vary but never exceed 16.. Data over the 'Claim
Sheet' 1661 rows will be added and deleted periodically but should never
exceed 16 rows at the same time.

Any help would be appreciated.. Is there a code I can just paste into the VB?

As previously mentioned I have achieved the results with formulae but it has
really slowed down the working of the work sheet! So any 'simple' alternative
method would be a great help

"John James" wrote:


If you want to delete blank rows on your datasheet, here's a routine
which does this:

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

If your formulae are slowing down your worksheet, then maybe try
getting the same result but without formulae. For instance if you
filter your data on your datasheet, then you'll be able to copy the
filtered range onto another sheet. If this meets your needs, you could
look at attaching this filter & paste process to a macro/button.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532460

Hi Dannycol,

OK that's clearer. Ignore my previous posting.

Here's one way:

In CW10 enter this formula
=IF(COUNTA(CM10:CV10)=0,0,1)

Copy this formula to the cells in the table below.
Place headings for your data in CM9 to CW9
Whilst in this table range, select Data-Filter-Autofilter
From the drop-down box in CW9 select "1"
Only the non-blank rows in your table will be visible and you'll be
able to copy that range to your target area.

Cheers,


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532460

Hi John

Many thanks for the info.. I'm almost there.. when you say copy this formula
into the table below.. do you mean into CW11:CW1661? this is what i've done.
When selecting auto filter i get drop down lists in all 10 columns..
Selecting '1' from CW9 does not do anything..? however selecting 'none blank'
from one of the other columns does work fine..

The only problem i have (or other users) is having to go thro' auto filter
etc every time the data has changed (added or deleted) in order to view the
completed list without blank rows. I played around with it using custom
option or sorting top 10 hoping i could leave auto filter selected and the
data apearring in the list without blank rows.. any ideas? or have I done
something wrong?

The aim is just to view this sheet with the listed data shown without blank
rows.

Can auto filter be left on without showing the drop down arrows?


"John James" wrote:


Hi Dannycol,

OK that's clearer. Ignore my previous posting.

Here's one way:

In CW10 enter this formula
=IF(COUNTA(CM10:CV10)=0,0,1)

Copy this formula to the cells in the table below.
Place headings for your data in CM9 to CW9
Whilst in this table range, select Data-Filter-Autofilter
From the drop-down box in CW9 select "1"
Only the non-blank rows in your table will be visible and you'll be
able to copy that range to your target area.

Cheers,


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532460

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