Home |
Search |
Today's Posts |
#1
|
|||
|
|||
listing data without blank rows
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing data without blank rows
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing data without blank rows
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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing data without blank rows
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing data without blank rows
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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing data without blank rows
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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing data without blank rows
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use outline data (grouped rows) in a protected worksheet? | Excel Discussion (Misc queries) | |||
Multiple rows of data on a single axis (charting) | Charts and Charting in Excel | |||
Blank Rows | Excel Discussion (Misc queries) | |||
How do I remove blank rows in Excel? | Excel Discussion (Misc queries) | |||
Inserting Blank Rows Macro? | Excel Worksheet Functions |