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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 12:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com