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.

 

[Fluff]

In that case it's best to state then when starting a post.

 

[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))

In that case it's best to state then when starting a post.

Yes, I should have mentioned it. But I only found out that TOCOL doesn't work when I tried the function.

 

[Peter_SSs]

But I only found out that TOCOL doesn't work when I tried the function.

.. but if you had said in post 1 that you needed the solution to work in 2021 I would not have even suggested TOCOL.

Whenever you ask a question, if you need/want it to work in any version(s) different to what is shown in your profile then you should say so up front so people do not waste their time on suggestions that are not going to suit your purpose.

 

[Peter_SSs]

@srizki
I have removed the 'Mark as solution' tick from post #13 as it clearly is not the answer to your question. Please mark the actual post that best answered your original question.