Formula not picking up a value on another worksheet

[Gorgie_boy]

Hi Guys, this is nuts - please please help.

I have a fairly complex workbook. The last worksheet picks up stats based on the values in 4 other worksheets. For one of the values, the following piece of formula correctly picks up the data I need for a chap called Keith.

=SUMPRODUCT(--('Phase 1 Bug_Logger'!D4:D5000="Keith"),--('Phase 1 Bug_Logger'!H4:H5000="Low"),1-ISNUMBER(MATCH('Phase 1 Bug_Logger'!F4:F5000,{"rejected","Closed","Deferred"},0)))

All the formulas are quite similar, in that if I change "Keith" to "Gerry" I get Gerrys figures etc. But when I change "Keith" to "Paul" (Thats me) I am not receiving values from the worksheet when all data is identical for each user.

I have made sure of the following:

- The ranges of cells/columns are correct
- spelling is consistent between formula and cell

Why is this happening. I need to get this working so all help greatly appreciated.

Regards

 

[Aladin Akyurek]

Extraneous spaces or invisible chars around the entries might be the culprit, which you can remove with the free ASAP Utilities (asap-utilities.com)

 

[Gorgie_boy]

Aladin, thanks you were right - I thought I had covered this by checking spaces in the formula, but because the "Paul" value was being generated from a List of values (Validation Menu) it was stored in the list as Paul with a space after it.

Thanks for your help.