What I have currently (new projects get added to as a new column, so a new project would then go into Column I): make sure the copy and paste areas don't overlap unless they are the same size and shape. The error message says: Run-time error '1004': This selection isn't valid. Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True I tried to record a macro and this is what I recorded: Sub TransposeColToRow() I'm looking to transpose my columns to rows from a sheet named "Tasks" to the sheet named "Assessment Changes". If I put the sheet name in the formula, then Excel can copy and transpose between sheets just using Excel's paste-special transpose command.ġ) Can I get Formula Manager to "add" the source sheet to the cell names when I copy and transpose? If not, then:Ģ) What would be a good way to automatically add "Sheet1!" to a row of 6 cells on 18 "tables"? (In other words, not manually) If I did this, then I could use either Excels transpose or formula managers transpose to send the data to sheet 2.I have a constantly-updating spreadsheet I use to track projects, but I want to create a summary view for internal stakeholders. However, I would not need Formula Manager for this. If I put "sheet 1" in my formula on sheet 1, =1/(Sheet1!$B9/(1000*0.95)), and then copy and transpose that formula to sheet 2, then it works great, as it uses B9 from sheet 1. I was using my sheet 1 formula: =1/($B9/(1000*0.95)), and transposing it to Sheet 2, but I was expecting the transpose/copy to insert Sheet1 in front of the $B9, it was not, so $B9 was now coming from sheet 2. I had just A1 on sheet 1 and not =Sheet1!A1. I was copying and transposing a range of cells from sheet 1 to sheet 2, but the source on sheet 1 DID NOT have its own sheet name on it, e.g. I tried the example you provided, it worked for me just fine! I then noticed the difference in what I was trying to do: Note: Excel can do what I need but it is cumbersome:ġ) I select the range of cells at the destination in sheet 10Ģ) Then I do a īut this gives me the numbers, but not the formula, and it is time consuming. > How can I keep the "sheet" information while I transpose? <. I would like each cell reference in the formula to keep the sheet it comes from. When I click on "Transpose", the formulas are transposed, but the reference to sheet 1 is lost, the formulas have no reference, so the transposed formula is getting its data from sheet 10 and not sheet 1. Here is what happens, using the Formula Manager:ġ) I select my "copy range", it looks like this: 'sheet 1'!$B$11:$G$11Ģ) Then I change to sheet 10, and select my "destination range start" it looks like this: 'sheet 10'!$AJ$5 I just purchased OzGrid's Formula Manager to help me with this, but it is not working. I am trying to select a row of 6 cells on one sheet (that are defined with a formula), and transpose them onto another sheet, to make a column.
0 kommentar(er)
