Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposing in formula
I have a formula that I need to copy along a row. Part of the formula refers
to cells that are elsewhere in a column. Is there a way of copying the formula along the row without having to then manually change the references for the data in the column. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposing in formula
Need more detail. Post your start cell formula, and describe what you want to
happen when you copy it across. Meantime, as an example of a dynamic transpose using OFFSET .. In Sheet2, you can place this in any starting cell, say, in B2: =OFFSET(Sheet1!$A$1,COLUMNS($A:A)-1,) When you copy B2 across (ie "rowwise"), it'll return the "columnwise" contents from Sheet1's A1, A2, A3, etc (viz it'll return a dynamic transpose of Sheet1's col A) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,400 Files:358 Subscribers:55 xdemechanik --- "Ken G." wrote: I have a formula that I need to copy along a row. Part of the formula refers to cells that are elsewhere in a column. Is there a way of copying the formula along the row without having to then manually change the references for the data in the column. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposing in formula
OK. Simplified, the formula in say A1 is =$A$10+ A11 Data exists in column A in cells A11....A15 When I copy the formula in A1 across B1, C1, D1 & E1 it will become $A$10+B11, $A$10+B12, $A$10+B13 etc. whereas I want it to be $A$10+A11, $A$10+A12 etc. "Max" wrote: Need more detail. Post your start cell formula, and describe what you want to happen when you copy it across. Meantime, as an example of a dynamic transpose using OFFSET .. In Sheet2, you can place this in any starting cell, say, in B2: =OFFSET(Sheet1!$A$1,COLUMNS($A:A)-1,) When you copy B2 across (ie "rowwise"), it'll return the "columnwise" contents from Sheet1's A1, A2, A3, etc (viz it'll return a dynamic transpose of Sheet1's col A) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,400 Files:358 Subscribers:55 xdemechanik --- "Ken G." wrote: I have a formula that I need to copy along a row. Part of the formula refers to cells that are elsewhere in a column. Is there a way of copying the formula along the row without having to then manually change the references for the data in the column. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposing in formula
Ok, you could replace the formula in A1 with this:
=$A$10+OFFSET($A$10,COLUMNS($A:A),) Copy A1 across, and you'd the required results Take a moment to press the "Yes" button below -- Max Singapore http://savefile.com/projects/236895 Downloads:17,400 Files:358 Subscribers:55 xdemechanik --- "Ken G." wrote: OK. Simplified, the formula in say A1 is =$A$10+ A11 Data exists in column A in cells A11....A15 When I copy the formula in A1 across B1, C1, D1 & E1 it will become $A$10+B11, $A$10+B12, $A$10+B13 etc. whereas I want it to be $A$10+A11, $A$10+A12 etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing | Excel Discussion (Misc queries) | |||
Transposing Cells which contains formula | Excel Worksheet Functions | |||
Transposing | Excel Worksheet Functions | |||
TRANSPOSING | Excel Discussion (Misc queries) | |||
Transposing cells | Excel Discussion (Misc queries) |