Transposing difficultings

[kfface]

I am trying to transpose a row of cells but to keep the new cells so that they show where the numbers were pulled from. I have tried using both index and offset functions and both of them I am close but I cant quite get the formulas to work out for me. Here is my last formula that I have been working with
=INDEX('[book1.xlsx]sheet1'!$B$2:$B$10,COLUMN()-COLUMN($F$21),1)
So what I am attempting to do is take data from B2-B10 and transpose it to F21-N21 of a different worksheet.
Any help would be great, thank you!

 

[kfface]

Sorry everyone I solved my issue, and am trying to delete this post.

 

[encioman]

how is it?
is your problem before same as mine...

I need a formula like index-match...how is it?

Sheet #1
A	1	SUGAR
B	2	COFFEE
C	3	SALT
D	4	OIL
Sheet #2
	1	2	3	4
C			SALT
B		COFFEE
A	SUGAR
D				OIL

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>