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

woodzilla

New Member
Joined
Jun 6, 2002
Messages
17
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Do you mean copy the values across?

You can either do loads of litle formulas:

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

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

New Member
Joined
Jun 6, 2002
Messages
17
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,143,617
Messages
5,719,735
Members
422,242
Latest member
hishamkhatri

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top