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!

In article <[email protected]>,
Tiesthatbind
<[email protected]> wrote:

> 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!

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

In article <[email protected]>,
Tiesthatbind
<[email protected]> wrote:

> 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]

In article <[email protected]>,
Tiesthatbind
<[email protected]> wrote:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> =INDEX('Itemval'!$B$5:$B$149,COLUMNS('Sheet1'!$G2:G2))
>
> Hope this helps!
>
> In article <[email protected]>,
> Tiesthatbind
> <[email protected]> wrote:
>
>> 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