Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
traspose data with formulas
I have the following data in sheet 1
Column A 1 2 3 4 5 I need to transpose the data of sheet 1 to sheet 2 but I need to retain the cell references so that whenever there is a change of data in sheet 1, sheet 2 is automatically updated. In sheet 2, i need to get the data like this Column A B C D E 1 2 3 4 5. If any of u have an answer, please post the reply. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
traspose data with formulas
Try this:
Assuming your data starts in Sheet1 in cell A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) Copy across as needed. -- Biff Microsoft Excel MVP "Darshan" wrote in message ... I have the following data in sheet 1 Column A 1 2 3 4 5 I need to transpose the data of sheet 1 to sheet 2 but I need to retain the cell references so that whenever there is a change of data in sheet 1, sheet 2 is automatically updated. In sheet 2, i need to get the data like this Column A B C D E 1 2 3 4 5. If any of u have an answer, please post the reply. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
traspose data with formulas
Thanks for the reply. It worked!!!.
I have another query, the same data which is transposed needs to be transposed into multiple rows, i.e Column A 1 2 3 4 5 6 7 8 9 10 I need to transpose this data in Sheet 2 in multiple rows ie. for items upto 1-4 in Row1 and 5-8 in Row 2 and 9-10 in Row 3. Can you please let me know how this can be done. "T. Valko" wrote: Try this: Assuming your data starts in Sheet1 in cell A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) Copy across as needed. -- Biff Microsoft Excel MVP "Darshan" wrote in message ... I have the following data in sheet 1 Column A 1 2 3 4 5 I need to transpose the data of sheet 1 to sheet 2 but I need to retain the cell references so that whenever there is a change of data in sheet 1, sheet 2 is automatically updated. In sheet 2, i need to get the data like this Column A B C D E 1 2 3 4 5. If any of u have an answer, please post the reply. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
traspose data with formulas
Try this:
=INDEX(Sheet1!$A:$A,ROWS($1:1)*4-4+COLUMNS($A:A)) Copy across to a total of 4 cells then down as needed. Note that based on your sample row 3 will return these values: 9...10...0...0 If you don't want the 0s try this formula: =IF(INDEX(Sheet1!$A:$A,ROWS($1:1)*4-4+COLUMNS($A:A))=0,"",INDEX(Sheet1!$A:$A,ROWS($1:1 )*4-4+COLUMNS($A:A))) -- Biff Microsoft Excel MVP "Darshan" wrote in message ... Thanks for the reply. It worked!!!. I have another query, the same data which is transposed needs to be transposed into multiple rows, i.e Column A 1 2 3 4 5 6 7 8 9 10 I need to transpose this data in Sheet 2 in multiple rows ie. for items upto 1-4 in Row1 and 5-8 in Row 2 and 9-10 in Row 3. Can you please let me know how this can be done. "T. Valko" wrote: Try this: Assuming your data starts in Sheet1 in cell A1: =INDEX(Sheet1!$A:$A,COLUMNS($A:A)) Copy across as needed. -- Biff Microsoft Excel MVP "Darshan" wrote in message ... I have the following data in sheet 1 Column A 1 2 3 4 5 I need to transpose the data of sheet 1 to sheet 2 but I need to retain the cell references so that whenever there is a change of data in sheet 1, sheet 2 is automatically updated. In sheet 2, i need to get the data like this Column A B C D E 1 2 3 4 5. If any of u have an answer, please post the reply. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation formulas | Excel Discussion (Misc queries) | |||
data sheet formulas | Excel Worksheet Functions | |||
delete data, keep formulas | Excel Discussion (Misc queries) | |||
Save Data not Formulas | Excel Discussion (Misc queries) | |||
traspose a row by links | Excel Worksheet Functions |