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

[woodzilla]

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?

 

[DonkeyOte]

Do you mean copy the values across?

You can either do loads of litle formulas:

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

 

[DonkeyOte]

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]

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

 

[Aladin Akyurek]

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.
(5.) Activate Data|Filter|Advanced Filter.
(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]

Aladin

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.

thanks for your help

 

[Aladin Akyurek]

On 2002-09-28 10:24, woodzilla wrote:
Aladin

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.

thanks for your help

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.