# Have a range refer to a given row even if the row number changes?

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 Answer

Another option:

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

• Hi Jacob; the second formula was intentional - it addressed the second formula in the OP. – AdamL Aug 14 '14 at 6:27
• Identical but for one character. :-) – AdamL Aug 14 '14 at 8:43
• 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
• Although I note I had an error... the reference in the ROW function was incorrect. – AdamL Aug 14 '14 at 11:24
• 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