| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
