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