Formula - Drag down to get values from the same row.

[srizki]

I have values in sheet 2. I need a formula for on sheet 1, so when I drag the formula down it get values from sheet 2 from the same row.
For example,
Sheet2 row1has

0235

0258

0261

0179

0180

I want formula to do this.

0235

0258

0261

0179

0180

Please advise.

Thanks.

 

[DanteAmor]

How bout:

=TRANSPONER(Sheet2!A2:E2)

 

[Peter_SSs]

How bout:

=TRANSPONER(Sheet2!A2:E2)

Translating, it would be ..

=TRANSPOSE(Sheet2!A2:E2)

Another option
(With Dante's option above or mine below, no need to copy down - the results will just "spill" down)

srizki.xlsm
	A	B	C	D	E
1	0235	0258	0261	0179	0180
Sheet2

srizki.xlsm
	A
1	0235
2	0258
3	0261
4	0179
5	0180
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=TOCOL(Sheet2!A1:E1)
Dynamic array formulas.

 

[FormR]

Or if you did want a formula that you "drag down" rather than one that spills, you could try.

Book1
	A
1	0235
2	0258
3	0261
4	0179
5	0180
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=INDEX(Sheet2!$A$1:$E$1,ROWS(A$1:A1))

 

[DanteAmor]

Thanks Peter.
Must be

=TRANSPOSE(Sheet2!A2:E2)

 

[srizki]

Thank you all. Someone asked me this formula to drag down, I forgot why it was needed to drag down rather than transpose.
Transpose function is working well. I will suggest both formulas to the person who asked for it.
Thanks again for your help.

 

[srizki]

Translating, it would be ..

=TRANSPOSE(Sheet2!A2:E2)

Another option
(With Dante's option above or mine below, no need to copy down - the results will just "spill" down)

srizki.xlsm
	A	B	C	D	E
1	0235	0258	0261	0179	0180
Sheet2

srizki.xlsm
	A
1	0235
2	0258
3	0261
4	0179
5	0180
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=TOCOL(Sheet2!A1:E1)
Dynamic array formulas.

Hi Peter,
Thanks for your reply. Transpose formula works, but I am using Excel 21 that does not have TOCOL function. I think TOCOL function was introduced in 365.

 

[srizki]

Or if you did want a formula that you "drag down" rather than one that spills, you could try.

Book1
	A
1	0235
2	0258
3	0261
4	0179
5	0180
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=INDEX(Sheet2!$A$1:$E$1,ROWS(A$1:A1))

Thank you! that worked.

 

[Fluff]

I am using Excel 21

Then why does your profile say 365?

 

[srizki]

How bout:

=TRANSPONER(Sheet2!A2:E2)

Then why does your profile say 365?

I am at work, here they are using Excel 21.