Transposing in formula

[Ken G.]

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.

 

[Max]

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.]

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]

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