# summerize data from If(C5>0,C5,"") cells

#### woodzilla

##### New Member
I don't know what command to use for summary of data in this example, If(C5>0,C5,"") cells . I need to collect the numbers on a different sheet, and ignore the blank cells.
Anyone direct me to a command sequence to get this started?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### DonkeyOte

##### MrExcel MVP
Do you mean copy the values across?

You can either do loads of litle formulas:

IF(Sheet1!C1="","",sheet1!c1)

#### DonkeyOte

##### MrExcel MVP
Do you mean copy the values across?

You can either do loads of litle formulas:

IF(Sheet1!C1="","",sheet1!c1)

If you want it in vb

SUB SKIP BLANKS

DATA = SHEETS("SHEET1").RANGE("C1:C1000")
CR = 1

FOR EACH ANS IN DATA

IF ANS <> "" THEN

SHEETS("SHEET2").RANGE("C" & CR) = ANS

END IF

CR = CR + 1

NEXT ANS

#### DonkeyOte

##### MrExcel MVP
Do you mean copy the values across?

You can either do loads of litle formulas:

IF(Sheet1!C1="","",sheet1!c1)

If you want it in vb

SUB SKIP BLANKS

DATA = SHEETS("SHEET1").RANGE("C1:C1000")
CR = 1

FOR EACH ANS IN DATA

IF ANS <> "" THEN

SHEETS("SHEET2").RANGE("C" & CR) = ANS

END IF

CR = CR + 1

NEXT ANS

##### MrExcel MVP
On 2002-09-27 08:02, woodzilla wrote:
I don't know what command to use for summary of data in this example, If(C5>0,C5,"") cells . I need to collect the numbers on a different sheet, and ignore the blank cells.
Anyone direct me to a command sequence to get this started?

Let C4:C100 houses the data in the source sheet, Sheet1, with C4 housing a distinctly formatted label.

( 1.) In A1 in the destination sheet, Sheet2, enter: Copy.
( 2.) Make "Copy" bold and italic.
( 3.) In A2 enter:

=LEN(Sheet1!C5)

(4.) Activate A3.
(6.) Check Copy to another location.
(7.) Enter Sheet1!C4:C100 in the box for List range.
(8.) Enter A1:A2 in the box for Criteria range.
(9.) Leave Unique records only unchecked.

#### woodzilla

##### New Member

You're on the right track, of what it is I want to do. As best as I can describe, its "entering data across worksheets, that update when numbers are changed in the first worksheet, to the second worksheet"

I'm not sure exactly what LEN commands the program to do in this case, I did get the numbers to copy, ignoring the blank cells. I think I just need it to update dynamically.

##### MrExcel MVP
On 2002-09-28 10:24, woodzilla wrote:

You're on the right track, of what it is I want to do. As best as I can describe, its "entering data across worksheets, that update when numbers are changed in the first worksheet, to the second worksheet"

I'm not sure exactly what LEN commands the program to do in this case, I did get the numbers to copy, ignoring the blank cells. I think I just need it to update dynamically.

Empty cells have a LEN(gth) of 0. That criterion expression is TRUE when LEN computes a non-zero result.

By the way, Advanced Filter requires "manual refreshing", unless someone writes some VBA code that activates Adv Filter when source data changes to make it dynamic.

Replies
3
Views
129
Replies
9
Views
239
Replies
7
Views
204
Replies
5
Views
549
Replies
1
Views
340

1,181,690
Messages
5,931,465
Members
436,790
Latest member
aelbaitam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back