Get second row of a range using formula

**danbenedek** · 06-13-2013, 07:05 AM

Is there a way to get the second row of a range using formula?
Ex: I have A1: BC100 => A2:BC2

I tried using INDEX(A1:BC100, 2) but it did not work.

I am trying to define a name as the second row of another name

**Ace_XL** · 06-13-2013, 07:30 AM

Go with

=INDEX(INDEX($A$1:$BC$100,2,0),0))

**Pepe Le Mokko** · 06-13-2013, 07:42 AM

Almost there try =INDEX(A1:BC100, 2,) to define your name

( the 2nd comma is important) !

**danbenedek** · 06-14-2013, 05:37 AM

Thank you Pepe Le Mokko.

It worked

**danbenedek** · 06-14-2013, 05:48 AM

Maybe not related to this thread but:
Is there a way to get using formula to get a subrange of a range?

I have A1:BC1 and I want A1:Z1

I think this will work =INDEX(A1:BC1,1,1):INDEX(A1:BC1,1,31) but are there simpler ways?

**:) Sixthsense :)** · 06-14-2013, 05:50 AM

How you are fixing it to Z?

If it is manual fix then just enter the range manually

**danbenedek** · 06-14-2013, 02:06 PM

Not even the range A1:BC100 is fixed. (What i have is range name and not an address.) I just need the subrange with the second row without the last 2 columns, and I was wandering if there is a way to define the name based on another name.