B10 = 'High"

=SORT(CONCATENATE(B10,"!B3:", B10, "!AK42"), High!AL3:High!AL42, TRUE)

That returns the error SORT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 40, column count: 1.


=SORT(High!B3:High!AK42, High!AL3:High!AL42, TRUE)

Works without any problems. Is there an easier way to achieve what I'm doing, or can I make this work with some changes? I'm essentially trying to get a sorted array (B3:AK42 sorted by AL3:AL42) from a sheet (High) specified in a cell (B10).

  • 1
    You probably need to use INDIRECT  in the first one.
    – Scott
    Mar 26 '19 at 3:02
  • Same error unfortunately
    – SWebb
    Mar 26 '19 at 3:06

Your first parameter to the Sort function needs to be a range. The result of Concatenate is a string, so you need to do something to convert it. Indirect will do the trick, but Indirect converts a single cell reference, not a range. So, you need to use one Indirect for the first end of the range, and another Indirect for the other end.

=SORT(Indirect(B10&"!B3"):Indirect(B10&"!AK42"), High!AL3:High!AL42, TRUE)

(I used & notation instead of the Concatenate function. I find it easier to read.)

  • try it like this:

    =SORT(INDIRECT(B10 & "!B3:AK42"), High!AL3:AL42, 1)

    =SORT(INDIRECT(B10 & "!B3:AK42"), INDIRECT(B10 & "!AL3:AL42"), 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy