V-LOOKing UP!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a CASE list on Sheet 1 as:

CASE BRANCH ID#

Sheet 2 has a BRANCH list like:

BRANCH ID#-MISSING

I want the cell Sheet2!B2 to be lookingup through the complete list in
Sheet1 and return an "X" if ANY of the CASES in the corresponding branch in
Sheet2!A2 has no ID.

What sort of formula should I try?

Thanx in advance.
 
One way ..

Assuming data in Sheet1 in cols A to C, from row2 to row100 (say)
where blank cells in col C (ID#) means "no ID"

Then in Sheet2,
With "Branch" data running in A2 down

Put in B2:
=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=A2)*(Sheet1!$C$2:$C$100=""))>0,"X","")
Copy down
 
WOW! Max!
That was wonderful. By the way would the same formula also work if the ID is
non-numerical, e.g. B-2200?
 
Yes, of course.

Afterall we're checking only for blanks in col C in Sheet1,
via the part: (Sheet1!$C$2:$C$100="")
in the formula
 
UH OH!
Another related problem jumped out.
Can I add another criteria like:
Sheet1's column D has Amounts Outstanding. I don't want the "X" appearing in
Sheet2's cell B2 where ID# doesn't exist but the case is also no more
outstanding i.e. 0.

In other words, if the sheet2 is to identify the branches which have cases
with amounts still outstanding but IDs not provided in Sheet1. Consider an
extra column "D" in Sheet1 as to be showing the amount.
 
=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=A2)*(Sheet1!$C$2:$C$100="")*(Sheet1!$D$2:$D$100>0))>0,"X","")
 
Back
Top