Joined: 23 Apr 2009
Location: Sydney, Australia
|Posted: Wed Jul 25, 2012 5:44 pm Post subject:
|Immediately identifiable problem is your use of ROW(1:1).
This works in xl but not in Calc, where A1:AMJ1 must be used instead of 1:1.
However, I don't think using ROW(A1:AMJ1) will be any good either as it will simply keep returning 1 and SMALL(IF(EventsList=A$1;ROW(EventsList));ROW(1:1)) will only return the smallest value in the array generated by IF(EventsList=A$1;ROW(EventsList))
Perhaps you should be using something like SMALL(IF(EventsList=A$1;ROW(EventsList);"");ROW(EventsList)-MIN(ROW(EventsList))+1) instead of SMALL(IF(EventsList=A$1;ROW(EventsList));ROW(1:1)).
Also, how many columns makes up the EventsList named range?
The 2 in your formula (the INDEX function's third parameter) implies more than one column, while "EventsList=A$1" (the IF function's first parameter) implies one column.
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).