if statement - if name is on list and it has a $$ value

[ajm]

folks,

I need to set a Yes or No flag for a list of vendor numbers based upon whether or not they appear in another sheet and have an associated positive dollar value. I use this Yes/No flag to decide which statements I have to create and print at the end of each month.

Currently, I use a formula to match the vendor number to the second sheet:

=IF(NOT(ISERROR(MATCH(M72,'EFT Payments (No Vendor Nbr)'!J:J,0))),"No","Yes")[\code]
where column M contains a list of vendor numbers and column J on the other sheet has a similar list of numbers. so, if the number doesn't match, set the flag to No. 

this produces a large number of Yes flags where the vendor exists on the second sheet but may have no activity for the current month.

any suggestions?

 

[DanteAmor]

Try this


[TABLE="width: 850"]
<tbody>[TR]
[TD][/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=IF(SUMPRODUCT(('EFT Payments (No Vendor Nbr)'!J2:J15=M72)*(MONTH('EFT Payments (No Vendor Nbr)'!F2:F15)=MONTH(TODAY())))>0,"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]


Change "F" to the column where you have your dates on the sheet "EFT Payments (No Vendor Nbr)"

 

[ajm]

Thanks Dante.

 

[DanteAmor]

You're welcome, thanks for the feedback.