Lookup across sheets

Guys,

Big problem for me ........hopefully small problem for you....

I have numbers displayed in a column on one worksheet and I want to display them in a row on another worksheet.

The only way I have been able to do it is to individually set up each cell which is very time consuming. I've entered a few cells and then tried dragging the sequence but it doesn't work properly. As i drag across the row of the 1st sheet ( where I want to display the info ) the cell references increment across the row from the 2nd sheet ( which contains the info I want in columns ) rather than down the column.

Does this make sense ?

Can anyone help ?

Thanx for reply.

I tried what you suggest but it didnt do exactly what I want. Looks like I only gave you part of the story for the original problem... I forgot to mention that the info in columns is not complete ie it gets updated daily, so the column size increases by 1 value per day as time goes on. Your suggestion works as a snapshot of that moment but when I add a value tomorrow it doesn't update the row.

Any suggestion would be welcome

Thanks Big Bas this almost worked

cos I don't understand what this formula is doing can you just help me with one last bit

I want to pick up the data starting at sheet 1 cell M22 ( start of column information) and place it at sheet 2 cell F25 ( start of row)