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.

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.

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.

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.