Forumla works in all cells but 1

[Guest]

Thank you in advance for your help. I have this array formula in cells L2:L52
=COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A8,4),IF('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003<>"",MATCH('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,0))),ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2)+1))

What could be a reason that in cell L8 the formula does not work? It returns
zero. I have copied the formula from differrent cells and nothing works. I
changed the value in one of the cells (from 8225 to 8999) on "Spares Quotes
List V22 26Apr2007.xls" and then put the same value on the spreadsheet with
the formula and it worked. When I use 8225 as the value to lookup in cell A8
it does not work. I then put the value back to 8225 and again it did not
work. I'm stumped.

Thank you for your help.
Joe

 

[Guest]

A bit confused as L8 will compare with $A14 not $A8 but there is no reason
other than data or data type which would cause a mismatch. It's difficult to
believe it was a particular data value other than it not existing in the
match columns.

Without the actual w/sheet, it's hard (for me!) to do anymore.

 

[Guest]

Your right, if the formula was entered in L2 as it shows then it would look
at $A14. My fault for for not saying that this formula is what is in L8,
starting in L2 it has $A2. I did find the problem though in one of the cells
for the supplier it had #N/A but no formula (this row "P" does not have
formulas, don't know how it got there). As soon as I changed it to NA it
calculated. Thank you for your response and looking at this problem.

Joe

A bit confused as L8 will compare with $A14 not $A8 but there is no reason
other than data or data type which would cause a mismatch. It's difficult to
believe it was a particular data value other than it not existing in the
match columns.

Without the actual w/sheet, it's hard (for me!) to do anymore.

Thank you in advance for your help. I have this array formula in cells L2:L52
=COUNT(1/FREQUENCY(IF(LEFT('[Spares Quotes List V22
26Apr2007.xls]All'!$A$2:$A$5003,4)=RIGHT($A8,4),IF('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003<>"",MATCH('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,'[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003,0))),ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2:$P$5003)-ROW('[Spares Quotes List V22
26Apr2007.xls]All'!$P$2)+1))

What could be a reason that in cell L8 the formula does not work? It returns
zero. I have copied the formula from differrent cells and nothing works. I
changed the value in one of the cells (from 8225 to 8999) on "Spares Quotes
List V22 26Apr2007.xls" and then put the same value on the spreadsheet with
the formula and it worked. When I use 8225 as the value to lookup in cell A8
it does not work. I then put the value back to 8225 and again it did not
work. I'm stumped.

Thank you for your help.
Joe