MATCH
 

I have a formula like =MATCH("SLL";B2:B15000;1) and it did found the
right cell row earlier. I also used INDIRECT and OFFSET in combination
to this formula, and to test it work all right I pasted in other values
(yes, acending) where "SLL" wasn't included and checked again.

It still gave the same row! I expected an #N/A. I tried several others,
and they also still give the row they had before. I have run VBA
Application.CalculateFullRebuild without any changes

How could this be?

I checked if the reference was to an external ws, but no. When I run
Find "SLL" on that array, it doesn't find the "SLL", but the MATCH
function still does.


Any suggestions?

Regards
/Tskogstrom

../. If needed - here is the full formulas:

THE PROBLEM CELL:
=IF(ROW()<=HighestUsedRow;INDEX(INDIRECT("Cust!$A$ 1:$C$15000");MATCH(C10;OFFSET(INDIRECT("Cust!$A$1" );1;1;HighestCustomerRow-1;1);1)+1;3);"")


Where Named Range "HighestUsedRow":
=MAX(IF(ISBLANK(INDIRECT("Base!$A$1:$A$500"));0;1) *ROW(INDIRECT("Base!$A$1:$A$500")))-4

(Is correct - this just reduce the column in the sheet, it isn't
involved in this problem)


Where Named Range "HighestCustomerRow":
=COUNTA(OFFSET(INDIRECT("Cust!$A$1");0;0;15000;1))

(Formula auditing-Evaluate formula prove this to be Ok)


Kind regards
Tskogstrom

MATCH
 

Sorry, false alarm.
It was just one tiny little figure at the wrong place that caused it.
Took me 6 hours to find it.. Long formulas is tricky, but give huge
better prestanda than VBA ...
/Regards
tskogstrom

tskogstrom skrev:

I have a formula like =MATCH("SLL";B2:B15000;1) and it did found the
right cell row earlier. I also used INDIRECT and OFFSET in combination
to this formula, and to test it work all right I pasted in other values
(yes, acending) where "SLL" wasn't included and checked again.

It still gave the same row! I expected an #N/A. I tried several others,
and they also still give the row they had before. I have run VBA
Application.CalculateFullRebuild without any changes

How could this be?

I checked if the reference was to an external ws, but no. When I run
Find "SLL" on that array, it doesn't find the "SLL", but the MATCH
function still does.


Any suggestions?

Regards
/Tskogstrom

./. If needed - here is the full formulas:

THE PROBLEM CELL:
=IF(ROW()<=HighestUsedRow;INDEX(INDIRECT("Cust!$A$ 1:$C$15000");MATCH(C10;OFFSET(INDIRECT("Cust!$A$1" );1;1;HighestCustomerRow-1;1);1)+1;3);"")


Where Named Range "HighestUsedRow":
=MAX(IF(ISBLANK(INDIRECT("Base!$A$1:$A$500"));0;1) *ROW(INDIRECT("Base!$A$1:$A$500")))-4

(Is correct - this just reduce the column in the sheet, it isn't
involved in this problem)


Where Named Range "HighestCustomerRow":
=COUNTA(OFFSET(INDIRECT("Cust!$A$1");0;0;15000;1))

(Formula auditing-Evaluate formula prove this to be Ok)


Kind regards
Tskogstrom