IF ...only it would work

[Digory]

Dear All,
Great forum - hope someone can help with this.

I have data in same workbook, different sheet arranged in cols with assigned NAMES to ranges.

For example; range PROD is a list of product documentation:
product1101_dispatched
product1101_invoice
product1102_dispatched
product1102_invoice

Range OLD is a list of deleted products:
product1000
product1000
product1001
product1000

I am trying to build a matrix where cells will first interogate OLD and if there is an entry return a D (for deleted). If no entry in OLD then interogate PROD and count the number of entries using productNumber* to count all the product documents associated with that product. Assumption is that products will appear in only one range or not at all. I will then conditional format matrix with traffic light red=0, orange=D,Green>0.

Am trying to use this (for prod1101):
{=IF(OLD="product1001","D",COUNTIF(PROD,"=product1101*"))}

Where am I going wrong ?

D:-)

[Max]

Try:

=IF(ISNUMBER(MATCH("product1001",OLD,0)),"D",
COUNTIF(PROD,"product1101*"))

Not sure whether there was a typo in the post as to: "product1101"
(Should the 2 product references be the same?)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Digory" <[email protected]> wrote in
message news:[email protected]...
>
> Dear All,
> Great forum - hope someone can help with this.
>
> I have data in same workbook, different sheet arranged in cols with
> assigned NAMES to ranges.
>
> For example; range PROD is a list of product documentation:
> product1101_dispatched
> product1101_invoice
> product1102_dispatched
> product1102_invoice
>
> Range OLD is a list of deleted products:
> product1000
> product1000
> product1001
> product1000
>
> I am trying to build a matrix where cells will first interogate OLD and
> if there is an entry return a D (for deleted). If no entry in OLD then
> interogate PROD and count the number of entries using productNumber* to
> count all the product documents associated with that product. Assumption
> is that products will appear in only one range or not at all. I will
> then conditional format matrix with traffic light red=0,
> orange=D,Green>0.
>
> Am trying to use this (for prod1101):
> {=IF(OLD="product1001","D",COUNTIF(PROD,"=product1101*"))}
>
> Where am I going wrong ?
>
> D:-)
>
>
> --
> Digory
> ------------------------------------------------------------------------
> Digory's Profile:
http://www.excelforum.com/member.php...o&userid=16439
> View this thread: http://www.excelforum.com/showthread...hreadid=473676
>

[Biff]

Hi!

Try this: (normally entered, not an array)

=IF(ISNUMBER(MATCH("product1001",old,0)),"D",COUNTIF(prod,"product1101*"))

Are you sure the criteria are correct? product1001, product1101

Also, you'd be better off using cells to hold the criteria:

A1 = product1001
B1 = product1101

Then:

=IF(ISNUMBER(MATCH(A1,old,0)),"D",COUNTIF(prod,B1&"*"))

Biff

"Digory" <[email protected]> wrote in
message news:[email protected]...
>
> Dear All,
> Great forum - hope someone can help with this.
>
> I have data in same workbook, different sheet arranged in cols with
> assigned NAMES to ranges.
>
> For example; range PROD is a list of product documentation:
> product1101_dispatched
> product1101_invoice
> product1102_dispatched
> product1102_invoice
>
> Range OLD is a list of deleted products:
> product1000
> product1000
> product1001
> product1000
>
> I am trying to build a matrix where cells will first interogate OLD and
> if there is an entry return a D (for deleted). If no entry in OLD then
> interogate PROD and count the number of entries using productNumber* to
> count all the product documents associated with that product. Assumption
> is that products will appear in only one range or not at all. I will
> then conditional format matrix with traffic light red=0,
> orange=D,Green>0.
>
> Am trying to use this (for prod1101):
> {=IF(OLD="product1001","D",COUNTIF(PROD,"=product1101*"))}
>
> Where am I going wrong ?
>
> D:-)
>
>
> --
> Digory
> ------------------------------------------------------------------------
> Digory's Profile:
> http://www.excelforum.com/member.php...o&userid=16439
> View this thread: http://www.excelforum.com/showthread...hreadid=473676
>

[Digory]

Guys,

Thanks - that's excellent (& oops there was a typo in question)

The tip to use cells to hold the criteria has saved me heaps of effort.
I had tried this but failed and resorted to using a generic formula and then find / replace to make specific for each cell.

Ta muchly
D:-)