Formula to Return a Value Q

[John]

I have a database of Sales Mixes by Location by Sales Date. What I am trying
to achieve is to produce a simple table that will detail to be for what
Location and Sales date any Sales Mixes are missing. My Report will look
something like this

11/04/04 12/04/04 13/04/04
Location A No
Location B
Location C No

In the simple table above, because no Sales Mixes exist in my table for
Location A on 12th April then it will show No, similarly for Location C on
the 11th April. I am looking for a formula that will return this 'No' detail
for the relevant date

My Database with all the relevant Sale Products will be in a worksheet
called 'Sales Mix'. The locations will be detailed in Column A; Sales dates
in Column B. As we sell multiple Products and each product sold will have a
line within the database, there will be multiple lines with the same Sales
date by location

Any help in producing a formula that will return 'No' where relevant as
shown above would be appreciated

Thanks

John

 

[JE McGimpsey]

One way:

=IF(SUMPRODUCT(--('Sales Mix'!$A$1:$A$10000=$A2), --('Sales
Mix'!$B$1:$B$10000=B$1))=0, "No", "")

 

[John]

Thanks JE, works a treat

I've tried including a Range Name replacing your range 1:10000 (as my
database is dynamic) but I get a #Value! error when I do this. Would there
be any reason for this

Thanks

John

 

[JE McGimpsey]

Make sure your range names are the same size.

 

[John]

Thanks JE, that was my prob

Make sure your range names are the same size.