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

{=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

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



.

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



.

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



.



.

We can use the same basic formula with some minor tweaks.

Since there are now 2 conditions let's use a cell that will count how many
records meet the conditions.

Enter this formula in B18:

=SUMPRODUCT(--(Order!C2:C23="N"),--(Order!D2:D23=C16))

Then, the array formula in B19:

=IF(ROWS(B$19:B19)B$18,"",INDEX(Order!B:B,SMALL(I F(Order!C$2:C$23="N",IF(Order!D$2:D$23=C$16,ROW(Or der!C$2:C$23))),ROWS(B$19:B19))))

--
Biff
Microsoft Excel MVP


"Bullytt" wrote in message
...
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



.



.

Absolutely perfect!

Huge thanks

Bullytt

"T. Valko" wrote:

We can use the same basic formula with some minor tweaks.

Since there are now 2 conditions let's use a cell that will count how many
records meet the conditions.

Enter this formula in B18:

=SUMPRODUCT(--(Order!C2:C23="N"),--(Order!D2:D23=C16))

Then, the array formula in B19:

=IF(ROWS(B$19:B19)B$18,"",INDEX(Order!B:B,SMALL(I F(Order!C$2:C$23="N",IF(Order!D$2:D$23=C$16,ROW(Or der!C$2:C$23))),ROWS(B$19:B19))))

--
Biff
Microsoft Excel MVP


"Bullytt" wrote in message
...
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



.



.



.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bullytt" wrote in message
...
Absolutely perfect!

Huge thanks

Bullytt

"T. Valko" wrote:

We can use the same basic formula with some minor tweaks.

Since there are now 2 conditions let's use a cell that will count how
many
records meet the conditions.

Enter this formula in B18:

=SUMPRODUCT(--(Order!C2:C23="N"),--(Order!D2:D23=C16))

Then, the array formula in B19:

=IF(ROWS(B$19:B19)B$18,"",INDEX(Order!B:B,SMALL(I F(Order!C$2:C$23="N",IF(Order!D$2:D$23=C$16,ROW(Or der!C$2:C$23))),ROWS(B$19:B19))))

--
Biff
Microsoft Excel MVP


"Bullytt" wrote in message
...
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



.



.



.