Forum Discussion
Copy transpose formula between workbooks as text
Hi, I'm stuck with the above problem. I am copying various formulas from one worksheet to a worksheet in a different workbook. Each worksheet has the same worksheets and layouts on them.
To avoid the new sheet referencing data from the sheet in the original workbook, I have copied the whole sheet as formulas and pasted them into Notepad. I've then copied and pasted the text onto a blank sheet in the other workbook, and it has been working fine.
I now am stuck with getting SPILL errors from pasting the TRANSPOSE function as text. For example, a cell formula of: =transpose(b1:h1) returns a column with 7 cells with #SPILL, then 7 cells with the correct numbers. This occurs whether I used "ENTER" at the end of the formula or "CTRL-ALT-ENTER". It also occurs if I paste the text into a cell on the same spreadsheet.
I have a lot of worksheets to paste this into and would appreciate any help with sorting this error please.
Thanks
2 Replies
- Sorry, I meant each workbook has the same worksheet labels and data layout.
- I found a workaround. I created two templates: the first pasted formulas that excluded the transpose commands, then the second added them. But the cells with a transpose formula had to be in the last row of the template, otherwise they could cause spill errors.
