Array formula copying blanks as zero’s

[Struggling in Sheffield]

Hi all,
(Excel 2003)

I’m using an array formula to copy certain rows of data from a table at the
top of my worksheet, to another table further down the sheet (rows copied
dependent on a certain cell entry in the original top table):

{=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN())))}

This works fine except where there are blank cells in my transferred data
row. I need the cells to remain blank when transferred down the worksheet but
excel is turning the blank cells into a 0 (zero) in my lower table.

I’ve been trying to alter my formula to specifically check for blank cells
in my original table (to return a blank), and have also tinkered with working
an ISBLANK expression into my formula, but can’t hit the right combination to
get the formula to work.

Would appreciate any help to get me over this one.
Many thanks for looking.
Steve

 

[T. Valko]

What type of data is the formula returning? Is it text? Is it numeric? Can
it be both?

 

[Struggling in Sheffield]

Hi,
The transferred rows of data contain text, a date, whole numbers and numbers
to 2 decimal places so yes, it is both.

 

[T. Valko]

yes, it is both.

Well, that doesn't help matters!

Try this (array entered):

=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1)),"",IF(INDEX(INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN())),1)="","",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN()))))

 

[Struggling in Sheffield]

Cheers Biff, works a treat.

Well, that doesn't help matters!

Try this (array entered):

=IF(ISERROR(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1)),"",IF(INDEX(INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN())),1)="","",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C1089,ROW($C$3:$C$902)),1),COLUMN()))))

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!