1

I have a table like so:

     A    |   B
------------------
1 || Name | Age
2 || joe  | 28
3 || bob  | 14
4 || Size | Weight
5 || small| 150

And I have a formula to sort each section by column B:

=sort(Sheet1!A2:B3, 2, TRUE)

and

=sort(Sheet1!A5:B, 2, TRUE)

My problem is that, if I insert a new row below row 1, which is a new Name / Age entry in my table, I need to manually go in and change the ranges in my formulas to now be A2:B4 and A6:B

Is there a way that I can somehow refer to to my Size / Weight without using the row number, so that it's always absolutely referred even if the row number changes? Something like putting a label on it and using such label in the range?

So I'd like something like this:

=sort(Sheet1!A2:Size|Weight, 2, TRUE)
=sort(Sheet1!Size|Weight+1:B, 2, TRUE)
1

Another option:

=SORT(FILTER(Sheet1!A2:B,ROW(Sheet1!A2:A)<MATCH("Size",Sheet1!A:A,0)),2,1)

  • 1
    Hi Jacob; the second formula was intentional - it addressed the second formula in the OP. – AdamL Aug 14 '14 at 6:27
  • 1
    Identical but for one character. :-) – AdamL Aug 14 '14 at 8:43
  • 1
    Anywhere other than Sheet1. I think the only issue with your answer is using the A:A reference rather than Sheet1!A:A. – AdamL Aug 14 '14 at 11:20
  • 1
    Although I note I had an error... the reference in the ROW function was incorrect. – AdamL Aug 14 '14 at 11:24
  • 1
    Odd that I should +1? I thought the INDIRECT solution was perfectly valid, if entered anywhere on Sheet1 other than columns A or B. I described it as an "issue" rather than an "error". I suppose using the sheet name in one reference and not in the other sent mixed messages, but I wouldn't describe it as an error per se. – AdamL Aug 14 '14 at 20:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.