Help with a formula

  • Thread starter Thread starter Fred Scuttle
  • Start date Start date
F

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
 
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
 
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).
 
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
 
Back
Top