Index Row and return data in a Single Column
 

Hi All,

How do I Index a Row of Text values in one Sheet and Return the Text data to
a NEW Sheet in one Column?

Sample - Row of Text data - Sheet 1
Columns B C D E
RowNo. 1 41-42 50-51 55-56 59-60

If the above is misaligned: B1=41-42, C1=50-51, D1=55-56, E1=59-60

Expected Results - returned in Column A - New Sheet
Column A
RowNo.8 41-42
RowNo.9 50-51
RowNo.10 55-56
RowNo.11 59-60

Thanks
Sam


--
Message posted via http://www.officekb.com

Try...

A8, copied down:

=INDEX(Sheet1!$B$1:$E$1,ROWS($A$8:A8))

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

How do I Index a Row of Text values in one Sheet and Return the Text data to
a NEW Sheet in one Column?

Sample - Row of Text data - Sheet 1
Columns B C D E
RowNo. 1 41-42 50-51 55-56 59-60

If the above is misaligned: B1=41-42, C1=50-51, D1=55-56, E1=59-60

Expected Results - returned in Column A - New Sheet
Column A
RowNo.8 41-42
RowNo.9 50-51
RowNo.10 55-56
RowNo.11 59-60

Thanks
Sam

Do you just mean

=INDEX(Sheet1!$B$1:$E$1,,ROW()-7)

--
HTH

Bob Phillips

"Sam via OfficeKB.com" wrote in message
...

Hi All,

How do I Index a Row of Text values in one Sheet and Return the Text data
to
a NEW Sheet in one Column?

Sample - Row of Text data - Sheet 1
Columns B C D E
RowNo. 1 41-42 50-51 55-56 59-60

If the above is misaligned: B1=41-42, C1=50-51, D1=55-56, E1=59-60

Expected Results - returned in Column A - New Sheet
Column A
RowNo.8 41-42
RowNo.9 50-51
RowNo.10 55-56
RowNo.11 59-60

Thanks
Sam


--
Message posted via http://www.officekb.com

"Domenic" wrote in message
...
Try...

A8, copied down:

=INDEX(Sheet1!$B$1:$E$1,ROWS($A$8:A8))

Clever!

Hi Domenic,

Thank you very much - Formula works great!

A8, copied down:
=INDEX(Sheet1!$B$1:$E$1,ROWS($A$8:A8))

Cheers
Sam


Domenic wrote:
Try...

A8, copied down:

=INDEX(Sheet1!$B$1:$E$1,ROWS($A$8:A8))

Hope this helps!

Hi All,

[quoted text clipped - 16 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

Hi Bob,

Thank you for very much for alternative solution - also works great!

=INDEX(Sheet1!$B$1:$E$1,,ROW()-7)

Cheers
Sam


Bob Phillips wrote:
Do you just mean

=INDEX(Sheet1!$B$1:$E$1,,ROW()-7)

Hi All,

[quoted text clipped - 16 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

Domenic's is cleverer, mine is more understandable <vbg

--
HTH

Bob Phillips

"Sam via OfficeKB.com" wrote in message
...

Hi Bob,

Thank you for very much for alternative solution - also works great!

=INDEX(Sheet1!$B$1:$E$1,,ROW()-7)

Cheers
Sam


Bob Phillips wrote:
Do you just mean

=INDEX(Sheet1!$B$1:$E$1,,ROW()-7)

Hi All,

[quoted text clipped - 16 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1