Transpose row to read to column

[Tiesthatbind]

Hello the board, please forgive me if this subject has been posed
elsewhere.
How do I create a formula to read data from eg. G70:AR70 Sheet 1, to
Column G5 Sheet 2 and drag down to as many cells needed ?
one way I have tried is =INDIRECT(ADDRESS(70,7)) , which works on Sheet
1 and transposes and reads perfectly, however I am unfamilar with how to
put this formula onto Sheet 2, reading from Sheet one, and then it
doesn't drag down the column (reading across the Sheet1 row70). any
help from the very knowledgable members is appreciated!

 

[Domenic]

Try...

G5, copied down:

=INDEX('Sheet1'!$G$70:$AR$70,ROWS(G$5:G5))

Hope this helps!

Tiesthatbind

 

[Tiesthatbind]

Thank you very much Domenic! your formula has worked like a charm, oddly
it did not hold the ' ' either side of the sheet1. however, seems to
have no bearing on the effectiveness of the formula. thanks again for
your prompt reply.

 

[Tiesthatbind]

Hi all, I guess I need further help to reverse the formula. I now need
to take column B5 on tab 'Itemval' and have it read on to tab 'Sheet1'
.. Thought to take the Domenic contributed formula and do this:
=INDEX('Itemval'!$B5:$B$149,COLUMNS('SHEET1'!G$2:G2)). but when i drag
this formula across Sheet1 row 2, only the contents of Itemval B5 read
though. Where did I go wrong? Thank you. J

 

[Domenic]

Try...

=INDEX('Itemval'!$B$5:$B$149,COLUMNS('Sheet1'!$G2:G2))

Hope this helps!

Tiesthatbind

 

[Domenic]

Tiesthatbind

...oddly
it did not hold the ' ' either side of the sheet1.

Since the sheet name doesn't contain a space or other relevant
character, there's no need for single quotes. I put them there just in
case your sheet name differed from the one you posted and contained a
space, etc.

 

[Tiesthatbind]

That did it thanks so much!

 

[Ragdyer]

Just slightly more concise, with no restraints on the range to be copied:

=INDEX(Itemval!$B:$B,COLUMNS($A:E))