| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 31713 | 2003-03-29 22:28:00 | Excel - Transposing data from one file to another. | John W (523) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 132022 | 2003-03-29 22:28:00 | In one file called Daysheet, I list the column totals in cells F1 through to AL1. In another file, I want to transpose (shift from horizontal to vertical row) that data starting in cell E5. For some reason it will not accept the fact the transposed data is linked to the 1st spreadsheet, and I get a column of figures that will not update as the Daysheet enteries are added. So I have to go the long way around, open both files, windows split them into 2 planes, then formula each cell one at a time. Any help appreciated. |
John W (523) | ||
| 132023 | 2003-03-29 23:30:00 | You can link to cells in another workbook by using the "!" operator. In the second workbook use the formula =Daysheet.xls!F1 in cell E5 Cell E6 would contain =Daysheet.xls!G1 |
Elephant (599) | ||
| 132024 | 2003-03-30 00:15:00 | Using the ! command can be trying at times, especially if Sheet or File names are large. If you go to the relevant cell in the second Sheet and start the formula <=> then click into the cell in the first sheet, it will automatically complete the link. If you want to transpose data from columnar array to rows, I would suggest you transpose the data into a different part of Sheet 1, (Copy -Paste Special-Transpose) then use the process in Para 1 above to start the new data file on Sheet 2. You can then drag the formula cell across the range of cells you want, and because the layout is correct from the transposed section on Sheet 1 it will reform on sheet 2 without having to individually link each cell. HTH Graham Bockett |
GrahamB (750) | ||
| 132025 | 2003-03-30 00:48:00 | Thanks to both of you. Grahame, I hadnt thought of your idea, Ill give it ago. I tried the Formula in the w2nd sheet, then using Autofill to zoom down the column, but it didnt work either. So I tried varoius combinations of the F4 key, but that failed as well. Your idea of linking transposing the values to a sheet in the first workbook, then linking the 2nd file to those seems to be the way to go. Thanks again.......John. |
John W (523) | ||
| 1 | |||||