Transpose row to read to column
 

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


------------------------------------------------------------------------
Tiesthatbind's Profile: http://www.excelforum.com/member.php...o&userid=32360
View this thread: http://www.excelforum.com/showthread...hreadid=560397

Transpose row to read to column
 

Try...

G5, copied down:

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

Hope this helps!

In article ,
Tiesthatbind
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! :)

Transpose row to read to column
 

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


------------------------------------------------------------------------
Tiesthatbind's Profile: http://www.excelforum.com/member.php...o&userid=32360
View this thread: http://www.excelforum.com/showthread...hreadid=560397

Transpose row to read to column
 

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:G 2)). 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


--
Tiesthatbind


------------------------------------------------------------------------
Tiesthatbind's Profile: http://www.excelforum.com/member.php...o&userid=32360
View this thread: http://www.excelforum.com/showthread...hreadid=560397

Transpose row to read to column
 

Try...

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

Hope this helps!

In article ,
Tiesthatbind
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:G 2)). 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

Transpose row to read to column
 

In article ,
Tiesthatbind
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.

Transpose row to read to column
 

That did it thanks so much!:)


--
Tiesthatbind


------------------------------------------------------------------------
Tiesthatbind's Profile: http://www.excelforum.com/member.php...o&userid=32360
View this thread: http://www.excelforum.com/showthread...hreadid=560397

Transpose row to read to column
 

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" wrote in message
...
Try...

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

Hope this helps!

In article ,
Tiesthatbind
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:G 2)). 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