Somehow I must have created an array, but I don't know where

[JenniferMurphy]

I have been working on moderately complicated table. When I just now tried to sort it on one of the columns, I got this error:

I didn't intentionally define or create any arrays and, as far as I can tell, I don't have any array formulas.

Most of the table is data. There are formulas in only 3 columns. Two of them have calls to a UDF and then I find the Min, Max, and Ave of those values.

It looks like the third column may be the culprit, but I don't know why or how. But if I don't include it in the sort, the sort works without the error.

If I select any cell in that column, the entire column gets a kinda shaded border. The formula in each cell is the same:

=COUNTIF(NameHdr:NameFtr,NameHdr:NameFtr)>1

NameHdr is the cell just above the first cell in the Name column and NameFtr is the cell just below the last cell in the Name column. It displays "TRUE" if the corresponding text in the Name column has a duplicate in that column and "FALSE" otherwise. If I select any cell except the first cell containing that formula, the formula in the formula bar is greyed out and if I click there, is gets cleared.

Does that formula create an implicit array?

If so, is there any way to get around that?

Thanks

 

[RoryA]

You've got a function that returns an array and it's spilling automatically down the rows, which is why you can only edit the formula in the first cell. To avoid that you'd need to rewrite it so that it evaluates each row of the range in turn, rather than all at once - e.g. by using an INDEX function around it.

 

[JenniferMurphy]

You've got a function that returns an array and it's spilling automatically down the rows, which is why you can only edit the formula in the first cell. To avoid that you'd need to rewrite it so that it evaluates each row of the range in turn, rather than all at once - e.g. by using an INDEX function around it.

I don't understand why it's returning an array or how Index will fix it.

I have a table containing data about members of a club. The first column is the member's name. It's a voluntary list and members sign up and drop off periodically. I wanted to have a way to ne notified if I tried to add a name that was already in the list. The list is not sorted alphabetically, so that formula was what I came up with. I'm sure there is a better way, but that what I was able to think of.

 

[RoryA]

NameHdr:NameFtr is a multi-cell range. Since you are passing that as the criterion, you will get an array of results - the counts for each of those cells.

If you've actually got a Table, I'm not sure why you wouldn't just use something like

=COUNTIF([NameColumn],[@NameColumn])>1

which would just evaluate for the relevant row of the table.

 

[JenniferMurphy]

NameHdr:NameFtr is a multi-cell range. Since you are passing that as the criterion, you will get an array of results - the counts for each of those cells.

If you've actually got a Table, I'm not sure why you wouldn't just use something like

=COUNTIF([NameColumn],[@NameColumn])>1

which would just evaluate for the relevant row of the table.

Thanks, that helped. I just forgot to make this resolved back then. ?