Help with a formula

[Fred Scuttle]

I have on worksheet3 of my template a name and address of
a client in cells C2 to C8
The data is pulled from a database using a DDE tool.
Some of the fields do not have data, only a zero.

I want to be able to show the name and address of my
customer on the front sheet of the template without the
zeros.

Example

Fred Scott
13 Willoby Chase
0
0
Whitby
North Yorks

should come out as

Fred Scott
13 Willoby Chase
Whitby
North Yorks

I think I need an array formula, but the help text is so
confusing.

PLease, any ideas gratefully accepted

Thanks In Advance

 

[Mark D]

Here are a few options that may work for you (in order of preference)
1) if you can use an if function in the original formula, eg =if(a1<>"",a1,"") will only carry forward actual cell value
2) For the range of cells where the 0's come in: Format>Cells>>Number>>Custom 0;0;"
3) For the range of cells : Format>Conditional Formatting> 'Cell Value Is' 'equal to' '0' in the three boxes, then set the format to white (or whatever your background color is

 

[Leo Heuser]

Fred

One way, assuming data in Sheet3 and front
sheet is Sheet1, where name and address is
in K2:K8.

In K2 enter

=IF(ROW()-ROW($K$2)<ROWS(Sheet3!$C$2:$C$8)-
COUNTIF(Sheet3!$C$2:$C$8,0),OFFSET(Sheet3!$C$2,
SMALL((IF(Sheet3!$C$2:$C$8 <>0,ROW(Sheet3!$C$2:$C$8)-
ROW(Sheet3!$C$2)+1)+0),ROW()-ROW($K$2)+1+
COUNTIF(Sheet3!$C$2:$C$8,0))-1,0),"")

The formula is, a you thought it would be, an array formula
and must be entered with <Shift><Ctrl><Enter>, also if
edited later. If entered correctly, Excel will display the formula
in the formula bar enclosed in curly brackets { } Don't enter
these brackets yourself.

Drag K2 down to K8 with the fill handle (the little square in
the lower right corner of the cell).

 

[Dan E]

Another option.

With data to be brought over in sheet1 C2:C8 and this function on
any sheet cell D2 (if you want to change where it is you'll need to
change the $D$2 below it'll be the first cell in the series)

=IF(ROW()<=SUMPRODUCT((ISNONTEXT(Sheet1!$C$2:$C$8)=FALSE)*1)
+ROW($D$2)-1,OFFSET(Sheet1!$C$2,SMALL(IF((ISNONTEXT(Sheet1!$C$2:$C$8)
=TRUE),"",(ISNONTEXT(Sheet1!$C$2:$C$8)=FALSE)*{0;1;2;3;4;5;6}),{1;2;3;4;5;6;7}),0),"")

Enter this formula then select the cell it's in and the 6 below (7 selected)
and array enter.
Array Entered (control + shift + enter)

Dan E