V-LOOKing UP!
 

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.

V-LOOKing UP!
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:

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.

V-LOOKing UP!
 

WOW! Max!
That was wonderful. By the way would the same formula also work if the ID is
non-numerical, e.g. B-2200?

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:

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.

V-LOOKing UP!
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
WOW! Max!
That was wonderful. By the way would the same formula also work if the ID is
non-numerical, e.g. B-2200?

V-LOOKing UP!
 

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.

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:

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.

V-LOOKing UP!
 

=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=A2)*(Sheet1!$C$ 2:$C$100="")*(Sheet1!$D$2:$D$1000))0,"X","")

"FARAZ QURESHI" wrote:

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.

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:

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.