Hi,
The purpose of FIND is to get the position of a substring in a longer
string. What do you want to achieve? The use of FIND in an array
function looks strange.
Kind regards
Regina
ouch schrieb:
I have an array formula that is returning an error of #VALUE when it
shouldn't.The formula is below.
{=ISERROR(INDEX($'Sales 4-10-2017'.$A$1:$A$1000;SMALL(IF(ISERROR(FIND("Discount";$'Sales
4-10-2017'.$A$1:$A$1000))=0;ROW($'Sales 4-10-2017'.$A$1:$A$1000);"");ROW(A200))))}
Digging into it, it seems Find is culprit of the #Value error. However that
initial iserror is not catching the error and the error propagates throughout
the formula even overriding the error #504 on index caused by the Find function
resulting in an invalid row being returned due to the error.
I put that final iserror around the index function just to show that something
is not right. The formula still returns #VALUE with that on there when it
obviously should be returning either true or false regardless of what the rest
of the formula is doing.
What is strange is if you move the cursor through the formula in the function
wizard it gets the expected results of true or false. But on the actual
spreadsheet you get that #VALUE error.
Oh, I just tried switching out Find for Search and the same thing occurs. So
maybe it's a bug in iserror as it's the only constant?
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org
