How to Link Sheet2 Rows to Sheet 1 Columns???

Hi everyone - first of all, I am a relative noob with excel, and I've done a search but haven't found an answer to this problem, so I apologize if this has been covered in a previous thread. Here's my problem:

I have data on Sheet1 that I want to link to Sheet2. BUT the Sheet1 data is sorted in columns and need to link them to ROWS in Sheet2. I need to write a formula in Sheet2 cells to link to the Sheet1 cells that I can "drag" so that the formulas auto-fill to the next corresponding cell as I have hundreds of rows and thousands of cells to link (obviously linking each and every cell would take forever). For example:

Sheet2 cells C10, D10, E10 F10 need to point and link to the data (respectively) to:
Sheet1 cells B20, B21, B22, B23

And again, I need a formula that I can just "drag" across the row to link each cell the corresponding cell on the other sheet. HELP!!

I'm not familiar with advanced macros, so a specific formula according to the example above would be helpful. Thanks so much!

One way would be to Copy the data, then do a Paste Special... Transpose.

A formula drive method - in C10

Please Login or Register  to view this content.

Sorry for the ignorance on my part - what do the -3 and -10 represent in the code?

Originally Posted by mdbct

One way would be to Copy the data, then do a Paste Special... Transpose.

A formula drive method - in C10

Please Login or Register  to view this content.

Someone also suggested I use the "INDEX" function, but he had no idea how?! Looking it up on MS help yielded more questions than answers...

Originally Posted by mdbct

One way would be to Copy the data, then do a Paste Special... Transpose.

A formula drive method - in C10

Please Login or Register  to view this content.

Thanks! I'll play with it and give it a shot.