Ask Your Question

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

asked 2017-04-20 03:42:17 +0100

aristotle2600 gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-03-09 12:16:30 +0100

hedorfer gravatar image


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.

edit flag offensive delete link more


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

SM_Riga gravatar imageSM_Riga ( 2019-03-09 19:54:04 +0100 )edit

answered 2017-04-20 03:57:03 +0100

robleyd gravatar image

Have a look at the INDEX() function -

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-04-20 03:42:17 +0100

Seen: 432 times

Last updated: Mar 09