I have two sheets: pricelist and products. In products an "x" should
appear when in pricelist an "x" is added in the referenced packing
column. In both sheets the product names are to be found in column B.
These packings a

C=125 ml
D=250 ml
E=500 ml
F=500 ml+
G=1,0 liter
H=2,5 liter
I=5,0 liter
J=10,0 liter
K=0,75 kg
L=1,0 kg

In pricelist as well as in products there are empty spaces in each
productline, but since there is no tag ("x") in the concerning column,
there does not appear an "x" in the product column. Only when the
packing is 1,0 liter, the formula suddenly decides that both conditions
are true, where there is absolutely no "x" on empty spaces nowhere in
the pricelist-sheet...

My formula used is this one:


=IF(SUMPRODUCT(--(pricelist!$G$3:$G$250="x")*--(pricelist!$B$3:$B$250=$B3))<0;"x";"")

Ofcourse I can rule out all the blank spaces from being checked, by
adding IF(SUM($B3)="";"" to the formula, but I want to know why my
perfectly working formula is not working on the 1,0 liter column...
Anyone please?

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile: http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=527952

Herman,

It works fine for me (as I understand it), but I am in the UK and use the
comma delimiter, not semi-colon, and wonder if there is anything about that.

Can you try this array formula and see if this also suffers?

=IF(ISNUMBER(MATCH(1,(pricelist!$G$3:$G$250="x")*( pricelist!$B$3:$B$250=$B3)
,0)),"x","")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Herman56" wrote in
message ...

I have two sheets: pricelist and products. In products an "x" should
appear when in pricelist an "x" is added in the referenced packing
column. In both sheets the product names are to be found in column B.
These packings a

C=125 ml
D=250 ml
E=500 ml
F=500 ml+
G=1,0 liter
H=2,5 liter
I=5,0 liter
J=10,0 liter
K=0,75 kg
L=1,0 kg

In pricelist as well as in products there are empty spaces in each
productline, but since there is no tag ("x") in the concerning column,
there does not appear an "x" in the product column. Only when the
packing is 1,0 liter, the formula suddenly decides that both conditions
are true, where there is absolutely no "x" on empty spaces nowhere in
the pricelist-sheet...

My formula used is this one:



=IF(SUMPRODUCT(--(pricelist!$G$3:$G$250="x")*--(pricelist!$B$3:$B$250=$B3))<
0;"x";"")

Ofcourse I can rule out all the blank spaces from being checked, by
adding IF(SUM($B3)="";"" to the formula, but I want to know why my
perfectly working formula is not working on the 1,0 liter column...
Anyone please?

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile:
http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=527952

Hi Bob!

Thanks for your reply! Your IF/ISNUMBER/MATCH-formula got rid of the
disturbing x's in the 1,0 liter column, but got rid of all other x's as
well... :-( :-)
Since that is even further from home, I changed back to my original
formula... :-)

I did check the cell formats, but they are simular in each column
concerned in the products sheet and in the pricelist columns. I fail to
see any difference between f.i. the 1,0 liter column and the 0,5 liter
or the 10,0 liter column. Yet it is the only column affected by this
peculiar behaviour...

I tried clearing the cells, but this did not make a difference at
all... I still am puzzled why this is affecting only the 1,0 liter
column... I have changed all the formulas by adding the
IF($B3="";"";-part to it, and all columns are behaving correct now...
But it should not have been necessary at all... :-(

Changing back the formulas did make an "x" pop up immediately at the
rows without entries... When you have another idea about it's cause and
it's solution, I would love to hear it, Bob! From anyone, btw... :-D

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile: http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=527952

Hi Bob!

Thanks for your reply! Your IF/ISNUMBER/MATCH-formula got rid of the
disturbing x's in the 1,0 liter column, but got rid of all other x's as
well... :-( :-)
Since that is even further from home, I changed back to my original
formula... :-)

I did check the cell formats, but they are simular in each column
concerned in the products sheet and in the pricelist columns. I fail to
see any difference between f.i. the 1,0 liter column and the 0,5 liter
or the 10,0 liter column. Yet it is the only column affected by this
peculiar behaviour...

I tried clearing the cells, but this did not make a difference at
all... I still am puzzled why this is affecting only the 1,0 liter
column... I have changed all the formulas by adding the
IF($B3="";"";-part to it, and all columns are behaving correct now...
But it should not have been necessary at all... :-(

Changing back the formulas did make an "x" pop up immediately at the
rows without entries... When you have another idea about it's cause and
it's solution, I would love to hear it, Bob! From anyone, btw... :-D

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile: http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=527952

Send me the spreadsheet?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Herman56" wrote in
message ...

Hi Bob!

Thanks for your reply! Your IF/ISNUMBER/MATCH-formula got rid of the
disturbing x's in the 1,0 liter column, but got rid of all other x's as
well... :-( :-)
Since that is even further from home, I changed back to my original
formula... :-)

I did check the cell formats, but they are simular in each column
concerned in the products sheet and in the pricelist columns. I fail to
see any difference between f.i. the 1,0 liter column and the 0,5 liter
or the 10,0 liter column. Yet it is the only column affected by this
peculiar behaviour...

I tried clearing the cells, but this did not make a difference at
all... I still am puzzled why this is affecting only the 1,0 liter
column... I have changed all the formulas by adding the
IF($B3="";"";-part to it, and all columns are behaving correct now...
But it should not have been necessary at all... :-(

Changing back the formulas did make an "x" pop up immediately at the
rows without entries... When you have another idea about it's cause and
it's solution, I would love to hear it, Bob! From anyone, btw... :-D

Paul


--
Herman56
------------------------------------------------------------------------
Herman56's Profile:
http://www.excelforum.com/member.php...o&userid=31018
View this thread: http://www.excelforum.com/showthread...hreadid=527952