Any better way to do this?

[Guanjin Peter]

in each cell =SUMPRODUCT(--(db!$A$2:$A26=$B$1),--(db!$F$2:$F26=C$5),--(db!$G$2:$G26=C$6),--(db!$E$2:$E26=$A7),--(db!$D$2:$D26=$B7),db!$H$2:$H26)

is suppose to return values either 1,2 or 3

if it is 1, the cell value will be KG
if it is 2, the cell value will be BAG
if it is 3, the cell value will be PKT


VLOOKUP WON't work for me because it returns >>><TABLE style="WIDTH: 73pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=97 border=0 x:str><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><TBODY><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 73pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" width=97 height=26 x:err="#NAME?">#NAME?</TD></TR></TBODY></TABLE>
after go to each cell then press 'enter' key then it returns
1(or 2 or 3)

Unless i got a way to refresh the page....any idea guys?

protoV5.xls
	I	J	K	L
1	uomCode	Uom
2	1	KG
3	2	PKT
4	1	KG
5	3	BAG
6	1	KG
7	2	PKT
8	1	KG
9	3	BAG
10	2	PKT
11	3	BAG
12	2	PKT
13	1	KG
14	2	PKT
15	3	BAG
16	2	PKT
17	2	PKT
18	1	KG
19	2	PKT
20	2	PKT
21	3	BAG
22	1	KG
23	3	BAG
24	2	PKT
db

 

[Fazza]

Hi Peter. Suggest you try CHOOSE. Excel help refers. Fazza

 

[Guanjin Peter]

yup it works. Thanks!!!!

erm hi, can I make it return a blank "", instead of a #value?

 

[Fazza]

You're very welcome. Regarding some of your other recent posts, Peter. I see various questions and it seems the structure of the spreadsheet is leading to a complex solution. Lots of array (SUMPRODUCT) formulas. I have not looked at the details - and am not volunteering to do so - though for sure the task would be an order of magnitude easier if you can improve the set up of the data. And the reports too.

Regards, Fazza

 

[Guanjin Peter]

yes.....it's a headache...i'm trying to simplify it now....i'll repost it V5 of the excel once it's done...anyway regarding the #value, is there anyway to force it to display blank?

 

[Fazza]

Hadn't seen that new question, Peter. Thanks for mentioning it.

Why does it return #value? Input should be 1 or 2 or 3.

If not, it depends on what you are doing - there are MANY approaches.

Maybe

=IF(OR(A1={1,2,3}),CHOOSE(A1,"one","two","three"),"blank")

 

[Guanjin Peter]

Was trying:

=IF(CHOOSE(SUMPRODUCT(--(db!$A$2:$A57=$B$1),--(db!$F$2:$F57=E$5),--(db!$G$2:$G57=E$6),--(db!$E$2:$E57=$A7),--(db!$D$2:$D57=$B7),db!$J$2:$J57), "KG","PKT","BAG")="#VALUE!","BLANK","Not Blank")

the value is not NULL, not #VALUE!, Not 0...what on earth?

 

[Fazza]

Maybe best to keep it simpler. One column with the SUMPRODUCT and then the next column with the next step.

I guess you saw the formula I posted..

 

[Guanjin Peter]

I wonder how to do it...haha...Thanks for quick response
Using your formula...like this right?


=IF(OR(SUMPRODUCT(--(db!$A$2:$A57=$B$1),--(db!$F$2:$F57=C$5),--(db!$G$2:$G57=C$6),--(db!$E$2:$E57=$A7),--(db!$D$2:$D57=$B7),db!$J$2:$J57)={1,2,3}),CHOOSE(SUMPRODUCT(--(db!$A$2:$A57=$B$1),--(db!$F$2:$F57=C$5),--(db!$G$2:$G57=C$6),--(db!$E$2:$E57=$A7),--(db!$D$2:$D57=$B7),db!$J$2:$J57),"KG","PKT","BAG"),"Blank")

 

[Aladin Akyurek]

Was trying:

=IF(CHOOSE(SUMPRODUCT(--(db!$A$2:$A57=$B$1),--(db!$F$2:$F57=E$5),--(db!$G$2:$G57=E$6),--(db!$E$2:$E57=$A7),--(db!$D$2:$D57=$B7),db!$J$2:$J57), "KG","PKT","BAG")="#VALUE!","BLANK","Not Blank")

the value is not NULL, not #VALUE!, Not 0...what on earth?

=CHOOSE(SUMPRODUCT(...)+1,"","KG","PKT","BAG")

"" is a blank, which you can replace with "BLANK" if so desired.