If two cells don't equal each other, notify; if they do equal eachother, do a "sum"

[rbrown999]

I'm building a quoting tool for my sales organization and I want to
make sure I'm using the most recent pricelist. My operations group
sends out a spreadsheet with the most recent pricing that I save to a
specific location. Then I have a worksheet in my quoter workbook that
syncs with what operations sends when I do a "refresh all". There is
a unique identifier in the pricelist that identifies it as the most
recent version. What I want to do is compare the identifier in the
worksheet called "Database" against the identifier in the pricelist
and if they're not the same, say "NOMATCH" and if they are the same,
go ahead and sum the contents of some cells. Here's what I have
written, but it's not doing anything except returning "NOMATCH". What
have I done wrong?

=IF(ISERROR(MATCH(+Database!G7,'C:\saved location\[pricelist.xls]
pricelist'!$G$1,0)),"NOMATCH",(SUM(I19:I20)))

 

[dranon]

I'm building a quoting tool for my sales organization and I want to
make sure I'm using the most recent pricelist. My operations group
sends out a spreadsheet with the most recent pricing that I save to a
specific location. Then I have a worksheet in my quoter workbook that
syncs with what operations sends when I do a "refresh all". There is
a unique identifier in the pricelist that identifies it as the most
recent version. What I want to do is compare the identifier in the
worksheet called "Database" against the identifier in the pricelist
and if they're not the same, say "NOMATCH" and if they are the same,
go ahead and sum the contents of some cells. Here's what I have
written, but it's not doing anything except returning "NOMATCH". What
have I done wrong?

=IF(ISERROR(MATCH(+Database!G7,'C:\saved location\[pricelist.xls]
pricelist'!$G$1,0)),"NOMATCH",(SUM(I19:I20)))

Does it work if pricelist is open?

 

[Niek Otten]

Maybe:

=IF(Database!G7<>'C:\saved
location\[pricelist.xls]pricelist'!$G$1,"NOMATCH",SUM(I19:I20))

 

[Bernard Liengme]

Not sure why you are using MATCH to test if two items are equal.

=IF(Database!G7='C:\saved location\[pricelist.xls]pricelist'!$G$1,
SUM(I19:I20), "No match")

This is how I use MATCH:
In A1 I have the word "cherry"
In B1:B10 I have a list of items (apple, banana, cherry, grape...)
=MATCH(A1,B1:B10,0) returns the value 3 to tell me the position of the match

best wishes

 

[Shane Devenshire]

Hi,

Try this:

=IF(Database!G7='C:\saved
location\[pricelist.xls]pricelist'!G1,SUM(I19:I20),"No Match")

It's just like Niek's, except if your not copying the formula you can drop
the $ signs and change the logic a little. If you want to make this a little
shorter you could use a range name for G1, lets say ID and since you are only
adding 2 cells:

=IF(Database!G7='C:\saved location\pricelist.xls'!ID,I19+I20,"No Match")