software

I have two sheets: pricelist and products. In products an quot;xquot; should
appear when in pricelist an quot;xquot; 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 (quot;xquot;) in the concerning column,
there does not appear an quot;xquot; 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 quot;xquot; on empty spaces nowhere in
the pricelist-sheet...

My formula used is this one:=IF(SUMPRODUCT(--(pricelist!$G$3:$G$250=quot;xquot;)*--(pricelist!$B$3:$B$250=$B3))lt;gt;0;quot;xquot;;quot;quot;)

Ofcourse I can rule out all the blank spaces from being checked, by
adding IF(SUM($B3)=quot;quot;;quot;quot; 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: www.excelforum.com/member.php...oamp;userid=31018
View this thread: www.excelforum.com/showthread...hreadid=527952Herman,

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=quot;xquot;)*( pricelist!$B$3:$B$250=$B3)
,0)),quot;xquot;,quot;quot;)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Herman56quot; gt; wrote in
message ...
gt;
gt; I have two sheets: pricelist and products. In products an quot;xquot; should
gt; appear when in pricelist an quot;xquot; is added in the referenced packing
gt; column. In both sheets the product names are to be found in column B.
gt; These packings a
gt;
gt; C=125 ml
gt; D=250 ml
gt; E=500 ml
gt; F=500 ml
gt; G=1,0 liter
gt; H=2,5 liter
gt; I=5,0 liter
gt; J=10,0 liter
gt; K=0,75 kg
gt; L=1,0 kg
gt;
gt; In pricelist as well as in products there are empty spaces in each
gt; productline, but since there is no tag (quot;xquot;) in the concerning column,
gt; there does not appear an quot;xquot; in the product column. Only when the
gt; packing is 1,0 liter, the formula suddenly decides that both conditions
gt; are true, where there is absolutely no quot;xquot; on empty spaces nowhere in
gt; the pricelist-sheet...
gt;
gt; My formula used is this one:
gt;
gt;
gt;
=IF(SUMPRODUCT(--(pricelist!$G$3:$G$250=quot;xquot;)*--(pricelist!$B$3:$B$250=$B3))lt;
gt;0;quot;xquot;;quot;quot;)
gt;
gt; Ofcourse I can rule out all the blank spaces from being checked, by
gt; adding IF(SUM($B3)=quot;quot;;quot;quot; to the formula, but I want to know why my
gt; perfectly working formula is not working on the 1,0 liter column...
gt; Anyone please?
gt;
gt; Paul
gt;
gt;
gt; --
gt; Herman56
gt; ------------------------------------------------------------------------
gt; Herman56's Profile:
www.excelforum.com/member.php...oamp;userid=31018
gt; View this thread: www.excelforum.com/showthread...hreadid=527952
gt;

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=quot;quot;;quot;quot;;-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 quot;xquot; 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: www.excelforum.com/member.php...oamp;userid=31018
View this thread: 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=quot;quot;;quot;quot;;-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 quot;xquot; 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: www.excelforum.com/member.php...oamp;userid=31018
View this thread: www.excelforum.com/showthread...hreadid=527952Send me the spreadsheet?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Herman56quot; gt; wrote in
message ...
gt;
gt; Hi Bob!
gt;
gt; Thanks for your reply! Your IF/ISNUMBER/MATCH-formula got rid of the
gt; disturbing x's in the 1,0 liter column, but got rid of all other x's as
gt; well... :-( :-)
gt; Since that is even further from home, I changed back to my original
gt; formula... :-)
gt;
gt; I did check the cell formats, but they are simular in each column
gt; concerned in the products sheet and in the pricelist columns. I fail to
gt; see any difference between f.i. the 1,0 liter column and the 0,5 liter
gt; or the 10,0 liter column. Yet it is the only column affected by this
gt; peculiar behaviour...
gt;
gt; I tried clearing the cells, but this did not make a difference at
gt; all... I still am puzzled why this is affecting only the 1,0 liter
gt; column... I have changed all the formulas by adding the
gt; IF($B3=quot;quot;;quot;quot;;-part to it, and all columns are behaving correct now...
gt; But it should not have been necessary at all... :-(
gt;
gt; Changing back the formulas did make an quot;xquot; pop up immediately at the
gt; rows without entries... When you have another idea about it's cause and
gt; it's solution, I would love to hear it, Bob! From anyone, btw... :-D
gt;
gt; Paul
gt;
gt;
gt; --
gt; Herman56
gt; ------------------------------------------------------------------------
gt; Herman56's Profile:
www.excelforum.com/member.php...oamp;userid=31018
gt; View this thread: www.excelforum.com/showthread...hreadid=527952
gt;