SOLVED

Cannot use Named Range in SORT Constant

Copper Contributor

Hi, This is about the SORT CONSTANT used in the [sort_index] in the Excel SORT function.

Background:

I have calculated the [sort_index] as a Named Range (1 cell big) in another sheet.

Problem:

When I try to use that named range I get #NAME error and NO results

When I type in the [sort_index] MANUALLY every thing works well and my data is sorted successfully.

Query:

Is there a way to use a Named Range in the [sort_index] ? I wonder if there is some syntax I am missing here.

 

Evidence: The enclosed picture shows how I have calculated the Named Range successfully. Then I have used the SORT function working and not working.

 

Thanks for looking. I am keen to get a fix as I have many sequences to sort by.

Signed: @RaphaelBM

Cannot use the [sort_index]Cannot use the [sort_index]

4 Replies

@RaphaelBM 

 

If Setup!H17, which is a single cell, holds {3,6,5,7}, then clearly {3,6,5,7} cannot here represent an array, but rather an array stored as text, for example as the result of

 

=ARRAYTOTEXT(A1:D1,1)

 

with A1:D1 containing the integers 3, 6, 5 and 7.

 

What's more, the sort_index parameter of SORT should comprise a single value, not an array.

 

Regards

Thanks for your prompt message Jos Woolley. You  have convincingly indicated the problems with my samples but sadly have not indicated how to fix my problem.

I would appreciate you having another go at this one indicating what I may need to change to get this working. My objective, as was made clear already, is to somehow drive the SORT constant from the numbers I have generated already.

 

I'm sure I'm almost there. Syntax syntax!

Thanks for your help. Raphael

 

The workaround is to store the array constant directly in the named item.
best response confirmed by RaphaelBM (Copper Contributor)
Solution

@Patrick2788 Thanks again.

Jos Woolley hinted that I need to specify an array and not text that merely looks like an array with curly brackets. I went ahead and created array containing those numbers and IT WORKS!

 

Conclusion: You CAN use a named range in the sort constant - but the range must be an Array.

 

The pic below shows my success getting this working OK. Thanks for the swift attention. 

Problem solved. Raphael

 

SORT constant using a Named RangeSORT constant using a Named Range

1 best response

Accepted Solutions
best response confirmed by RaphaelBM (Copper Contributor)
Solution

@Patrick2788 Thanks again.

Jos Woolley hinted that I need to specify an array and not text that merely looks like an array with curly brackets. I went ahead and created array containing those numbers and IT WORKS!

 

Conclusion: You CAN use a named range in the sort constant - but the range must be an Array.

 

The pic below shows my success getting this working OK. Thanks for the swift attention. 

Problem solved. Raphael

 

SORT constant using a Named RangeSORT constant using a Named Range

View solution in original post