Is there a way to get a subrange from a given range, specifically a named range?

[aristotle2600]

OK, so let’s say I have a range, either A1:E5 or MainRange, if it’s named. Now I want just a part of that range, say the 2nd and 3rd columns, all but the last row. So, if my range covers the cells A1:E5, I want B1:C4. BUT, I want a function that I can pass the limits of my subrange to, and have it return the actual range. So, a function call that looks something like

SUBRANGE(A1:E5, 1,2,4, 2) (Main Range, first row, first column, number of rows, number of columns). I then use this function in any other function that needs a range. Is there a way to do this? Especially I’d like to be able to use a named range instead of something like A1:E5 above.

[hedorfer]

Hi,

almost two years later… I guess you already have found the solution, but maybe others, searching for the same issue, didn’t (like me, just yesterday).

Robleyd was right: looking at the INDEX() function was the correct way. But, nevertheless, it was hard for me to find out what exactly had to be done, since online documentation is not always very exhaustive. I understood that the INDEX() function is right when I realised that it does indeed not return values but references. That’s the trick!

So, say you want to calculate the maximum value of your subrange by using function MAX(), you have to define the relative cell indexes of the upper left and of the lower right corners inside your sub range which you called “MainRange”:

upper left corner: row 1; column 2

lower right corner: row 4; column 3

Then, using

INDEX(MainRange, 1, 2) and INDEX(MainRange, 3, 4)

will return the values of the two cells OR the references to the two cells. So, the sub range is defined by simply combining the two corners with a colon, such as:

=MAX(INDEX(MainRange, 1, 2):INDEX(MainRange, 3, 4))

I tested it and it works. I also tried other solutions with tildes (~) inside one single INDEX() function, but with no result other than errors or inconsistent values.

I hope this explanation will be useful for somebody…

Best wishes.

[SM_Riga]

I also tried other solutions with
tildes (~) inside one single INDEX()
function, but with no result other
than errors or inconsistent values.

An OFFSET function provides all the functionality needed inside one function. You can refer named range, define starting row/column inside this range and define size of the new subrange returned. So you formula could be =MAX(OFFSET(MainRange;0;1;4;2)) Here is a bit more about INDEX and OFFSET

[robleyd]

Have a look at the INDEX() function - Spreadsheet Functions - LibreOffice Help