| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Columna to Rows Formula
I have entered transactions in columns in Sheet1 and need to set up formulas
in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
For A6
=INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
Bob,
I entered the formula and the cell returns #NAME? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
Bob,
Actually the formula returns the first transaction in sheet1 column C in sheet2 Row 6 down to row 250 in the table??? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
Try
=INDEX(INDIRECT("Sheet1!$"&CHAR(61+ROW())&"$5:$"&C HAR(61+ROW())&"$59"),COLUMN()) in A6 (on Sheet2) and copy down and across "Mike" wrote: Bob, Actually the formula returns the first transaction in sheet1 column C in sheet2 Row 6 down to row 250 in the table??? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
Works perfectly down to row 30 then 30-35 is #REF!
then row 36 is 00000 whiich is ok then row 37 is colum headings then row38-60 repeats data from sheet1 then row 62-194 is #REF! then row 195-250 is #VALUE! I have currently input transactions into sheet1 Column C,D,F but will enter a few hundred transactions this week. Thus sheet2 row 6,7,8 should show the three transaction, which it does, but all other rows should show 0's "Sheeloo" wrote: Try =INDEX(INDIRECT("Sheet1!$"&CHAR(61+ROW())&"$5:$"&C HAR(61+ROW())&"$59"),COLUMN()) in A6 (on Sheet2) and copy down and across "Mike" wrote: Bob, Actually the formula returns the first transaction in sheet1 column C in sheet2 Row 6 down to row 250 in the table??? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
I did not think beyond column Z..
Try =INDEX(INDIRECT("Sheet1!R5C"&(ROW()-3)&":R59C"&(ROW()-3),FALSE),COLUMN()) This should work for all... "Mike" wrote: Works perfectly down to row 30 then 30-35 is #REF! then row 36 is 00000 whiich is ok then row 37 is colum headings then row38-60 repeats data from sheet1 then row 62-194 is #REF! then row 195-250 is #VALUE! I have currently input transactions into sheet1 Column C,D,F but will enter a few hundred transactions this week. Thus sheet2 row 6,7,8 should show the three transaction, which it does, but all other rows should show 0's "Sheeloo" wrote: Try =INDEX(INDIRECT("Sheet1!$"&CHAR(61+ROW())&"$5:$"&C HAR(61+ROW())&"$59"),COLUMN()) in A6 (on Sheet2) and copy down and across "Mike" wrote: Bob, Actually the formula returns the first transaction in sheet1 column C in sheet2 Row 6 down to row 250 in the table??? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
Sheeloo,
Works Perfect. But, I can't filter sort the data in the table. I think it is because of the INDIRECT in the formula??? Thanks, Mike "Sheeloo" wrote: I did not think beyond column Z.. Try =INDEX(INDIRECT("Sheet1!R5C"&(ROW()-3)&":R59C"&(ROW()-3),FALSE),COLUMN()) This should work for all... "Mike" wrote: Works perfectly down to row 30 then 30-35 is #REF! then row 36 is 00000 whiich is ok then row 37 is colum headings then row38-60 repeats data from sheet1 then row 62-194 is #REF! then row 195-250 is #VALUE! I have currently input transactions into sheet1 Column C,D,F but will enter a few hundred transactions this week. Thus sheet2 row 6,7,8 should show the three transaction, which it does, but all other rows should show 0's "Sheeloo" wrote: Try =INDEX(INDIRECT("Sheet1!$"&CHAR(61+ROW())&"$5:$"&C HAR(61+ROW())&"$59"),COLUMN()) in A6 (on Sheet2) and copy down and across "Mike" wrote: Bob, Actually the formula returns the first transaction in sheet1 column C in sheet2 Row 6 down to row 250 in the table??? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
You are partially right...
Since the formula is based on Row() sorting will not have any impact. Indirect just passes the string as an address to Index... You can copy and paste special as values if you want to sort but then updates in Sheet1 won't carry over. Filter should work... "Mike" wrote: Sheeloo, Works Perfect. But, I can't filter sort the data in the table. I think it is because of the INDIRECT in the formula??? Thanks, Mike "Sheeloo" wrote: I did not think beyond column Z.. Try =INDEX(INDIRECT("Sheet1!R5C"&(ROW()-3)&":R59C"&(ROW()-3),FALSE),COLUMN()) This should work for all... "Mike" wrote: Works perfectly down to row 30 then 30-35 is #REF! then row 36 is 00000 whiich is ok then row 37 is colum headings then row38-60 repeats data from sheet1 then row 62-194 is #REF! then row 195-250 is #VALUE! I have currently input transactions into sheet1 Column C,D,F but will enter a few hundred transactions this week. Thus sheet2 row 6,7,8 should show the three transaction, which it does, but all other rows should show 0's "Sheeloo" wrote: Try =INDEX(INDIRECT("Sheet1!$"&CHAR(61+ROW())&"$5:$"&C HAR(61+ROW())&"$59"),COLUMN()) in A6 (on Sheet2) and copy down and across "Mike" wrote: Bob, Actually the formula returns the first transaction in sheet1 column C in sheet2 Row 6 down to row 250 in the table??? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Columna to Rows Formula
Thank you very much!!!
I will copy and special paste values & number formats as reports are required. I was hoping there was a way to sort without doing that. Thanks, Mike "Sheeloo" wrote: You are partially right... Since the formula is based on Row() sorting will not have any impact. Indirect just passes the string as an address to Index... You can copy and paste special as values if you want to sort but then updates in Sheet1 won't carry over. Filter should work... "Mike" wrote: Sheeloo, Works Perfect. But, I can't filter sort the data in the table. I think it is because of the INDIRECT in the formula??? Thanks, Mike "Sheeloo" wrote: I did not think beyond column Z.. Try =INDEX(INDIRECT("Sheet1!R5C"&(ROW()-3)&":R59C"&(ROW()-3),FALSE),COLUMN()) This should work for all... "Mike" wrote: Works perfectly down to row 30 then 30-35 is #REF! then row 36 is 00000 whiich is ok then row 37 is colum headings then row38-60 repeats data from sheet1 then row 62-194 is #REF! then row 195-250 is #VALUE! I have currently input transactions into sheet1 Column C,D,F but will enter a few hundred transactions this week. Thus sheet2 row 6,7,8 should show the three transaction, which it does, but all other rows should show 0's "Sheeloo" wrote: Try =INDEX(INDIRECT("Sheet1!$"&CHAR(61+ROW())&"$5:$"&C HAR(61+ROW())&"$59"),COLUMN()) in A6 (on Sheet2) and copy down and across "Mike" wrote: Bob, Actually the formula returns the first transaction in sheet1 column C in sheet2 Row 6 down to row 250 in the table??? Mike "Bob Phillips" wrote: For A6 =INDEX(Sheet1!$C$5:$C$59,COLUMN()) and copy across, etc. -- __________________________________ HTH Bob "Mike" wrote in message ... I have entered transactions in columns in Sheet1 and need to set up formulas in Sheet2 to bring that data into Rows in order to set up a table for filtering and sorting. Example: Sheet1 the transactions are entered as folllows: C5:C59, D5:D59, E5:E59 and so on... I want those transactions to go into Sheet2, beggining at A6, in rows as follows: A6:BC6 would be Sheet1 C5:C59 A7:BC7 would be Sheet1 D5:D59 A8:BC8 would be Sheet1 E5:E59 and so on... Special formulas??? Your help is appreciated!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF value columnA columnB | Excel Worksheet Functions | |||
| sum up columnA depending on conditions on columnB and columnC | Excel Discussion (Misc queries) | |||
| Cambiar color fila dependiendo de valor de columna | Excel Discussion (Misc queries) | |||
| IF ColumnA = ltr Add Column B | Excel Worksheet Functions | |||
| Adding new numbers as I type without duplicates from Sheet1,ColumnA to Sheet2,ColumnA | Excel Worksheet Functions | |||
Linear Mode
