| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! :) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| how can i multiply two columns | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Linear Mode
