Formula not picking up a value on another worksheet

Gorgie_boy

New Member
Joined
Jul 7, 2005
Messages
19
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Jul 7, 2005
Messages
19
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,491
Members
412,669
Latest member
nickyon1
Top