T
tskogstrom
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
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