Superb stuff! - I managed to work out enough to use the same formula for
different columns in "Order".

Would this formula be the best place to add another condition?

There is an input in c16 on the same sheet for a customer number - this
number (to be matched against Order column D) should filter the results again
(the first filter being whether there is an "n" in "Order column C"

Once again many thanks for the first formula - I certainly would never be
able to create a solution like that!

Regards,

Bullytt

"T. Valko" wrote:

The formula is for the cell range B19:B32

Enter this array formula** in B19:

=IF(ROWS(B$19:B19)COUNTIF(Order!C$2:C$23,"N"),"", INDEX(Order!B:B,SMALL(IF(Order!C$2:C$23="N",ROW(Or der!C$2:C$23)),ROWS(B$19:B19))))

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Bullytt" wrote in message
...
HI - and thanks for a response.

The formula is for the cell range B19:B32 (used ctrl+shift+enter)
This range is on a sheet called "Statement" which is why I have to go to
sheet "Order" to fetch the data.

Hope that makes sense!

Bullytt

"T. Valko" wrote:

{=IF(Order!C2:C23="n",Order!B2:B23,0)}

What is the *exact* location of that formula?

--
Biff
Microsoft Excel MVP


"Bullytt" wrote in message
...
HI I am using a spreadsheet over several sheets to show a statement of
account.

I can use:{=IF(Order!C2:C23="n",Order!B2:B23,0)} to produce an array
that
checks column C for an "n" and displays the date (unpaid invoice date
as
it
happens) from column B. A zero is inserted if there is no "n"

I would like to know if there is a way to ONLY display the non-zero
cells:
12/12/09
14/12/09 etc rather than

0
0
12/12/09
0
0
0
14/12/09

That is showing at the moment.

Many thanks in advance for any helpful suggestions - bit of a novice in
over
my head!

Bullytt



.



.