Need help on a multiple occurrence array formula

kaybrimar · Newbie Joined: 25 Jul 2012 Posts: 2

This is what I have presently:

ken johnson · 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.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).